DSXchange

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

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.

Thanks for the inputs Ray! Loop and then Outerjoin worked