Hello Folks,
Need insights on approach for this
I have the below file which will have a Column1(which is like a Key);Date(Every Monday of the week);qty
I will need to have "N" number of weeks qty for each Column1 Key Value
Example Data:
Column1;Date;Qty
1;01222018;2
1;01292018;3
1;02052018;4
.
.
N
2;01222018;2
2;01292018;3
2;02052018;4
.
.
N
Lets say if the N value is 4. I will need to have 4 weeks records(1 record per each week) of each Key value(Column1) in the output. If any of the week in those 4 weeks is missing, I will have to insert that week with a Monday date and qty as Zero. I will always start from the lowest weeknumber to begin with
Example: when N = 4
Input:
1;01222018;2
1;02052018;4
2;01222018;2
2;01292018;3
Output:
1;01222018;2
1;01292018;0
1;02052018;4
1;02122018;0
2;01222018;2
2;01292018;3
2;02052018;0
2;02122018;0
Add missing Week Qty with default value
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 13
- Joined: Mon Oct 24, 2016 7:03 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
How about a loop in a Transformer stage? Traverse the loop exactly N times.
Or generate a temporary table with N combinations per row and perform an outer join to it, replacing returned NULL with 0.
Or generate a temporary table with N combinations per row and perform an outer join to it, replacing returned NULL with 0.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 13
- Joined: Mon Oct 24, 2016 7:03 am