Derive Multiple Rows from two columns based on Range
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Derive Multiple Rows from two columns based on Range
I have to derive Multiple Rows from two columns based on Range :
For Example:
Source File looks like:
Col1 Col 2 Col3
A 2004 2006
B 1999 2002
From this I have to a derive a column (Col4) which should have all values within the range of Col2 and col3
So My Output file should look like:
Col1 Col4
A 2004
A 2005
A 2006
B 1999
B 2000
B 2001
B 2002
I will appreciate if anybody can help me designing this logic efficiently in DataStage 7.0 server version.
Thanks in advance,
Take care!
For Example:
Source File looks like:
Col1 Col 2 Col3
A 2004 2006
B 1999 2002
From this I have to a derive a column (Col4) which should have all values within the range of Col2 and col3
So My Output file should look like:
Col1 Col4
A 2004
A 2005
A 2006
B 1999
B 2000
B 2001
B 2002
I will appreciate if anybody can help me designing this logic efficiently in DataStage 7.0 server version.
Thanks in advance,
Take care!
Last edited by just4geeks on Mon Sep 28, 2009 3:56 pm, edited 1 time in total.
Attitude is everything....
Re: Derive Multiple Rows from two columns based on Range
Is this a file you get or is it a table that is your source?just4geeks wrote:I have to derive Multiple Rows from two columns based on Range :
For Example:
Source File looks like:
Col1 Col 2 Col3
A 2004 2006
B 1999 2002
From this I have a derive a column (Col4) which should have all values within the range of Col2 and col3
So My Output file should look like:
Col1 Col4
A 2004
A 2005
A 2006
B 1999
B 2000
B 2001
B 2002
I will appreciate if anybody can help me designing this logic efficiently in DataStage 7.0 server version.
Thanks in advance,
Take care!
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
quick and easy solution.
Quick and dirty way would be to put it on a staging table and then do a between statement where you can pass the year number as a parameter which would yeild the desired result.just4geeks wrote:Source is a file.
Thanks!
If you do not want to use staging tables then tell me more about this range. Is it always a date or what values do these columns have?
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
What is your target?
Build a routine with a FOR loop, appending the values from your range. If your target is another sequential file, you can even add CHAR(10) characters between them to create individual records from the returned string. Crude illustration:
Hopefully that will get the creative juices flowing...
![Confused :?](./images/smilies/icon_confused.gif)
Build a routine with a FOR loop, appending the values from your range. If your target is another sequential file, you can even add CHAR(10) characters between them to create individual records from the returned string. Crude illustration:
Code: Select all
YearString=""
FOR year = Col2 to Col3 step 1
YearString := Col1:",":year:CHAR(10)
NEXT year
Ans=YearString
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom