DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
tradersjoe57



Group memberships:
Premium Members

Joined: 24 Oct 2016
Posts: 13

Points: 186

Post Posted: Thu Jan 25, 2018 11:07 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54221
Location: Sydney, Australia
Points: 294071

Post Posted: Sun Jan 28, 2018 7:10 pm Reply with quote    Back to top    

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 ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
tradersjoe57



Group memberships:
Premium Members

Joined: 24 Oct 2016
Posts: 13

Points: 186

Post Posted: Wed Jan 31, 2018 7:35 pm Reply with quote    Back to top    

Thanks for the inputs Ray! Loop and then Outerjoin worked
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours