Add missing Week Qty with default value

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
tradersjoe57
Premium Member
Premium Member
Posts: 13
Joined: Mon Oct 24, 2016 7:03 am

Add missing Week Qty with default value

Post by tradersjoe57 »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
tradersjoe57
Premium Member
Premium Member
Posts: 13
Joined: Mon Oct 24, 2016 7:03 am

Post by tradersjoe57 »

Thanks for the inputs Ray! Loop and then Outerjoin worked
Post Reply