Records in sequential order...
Moderators: chulett, rschirm, roy
Records in sequential order...
Hi All,
One of my source file column value like I mentioned below
Column1
----------
8,12-14
10,2-7
If there is any range in the column I need to load the data sequentialy in
beow mentioned format with proper delimiter.
Column1
----------
8,12,13,14
10,2,3,4,5,6,7
Any help is really appreciated..
One of my source file column value like I mentioned below
Column1
----------
8,12-14
10,2-7
If there is any range in the column I need to load the data sequentialy in
beow mentioned format with proper delimiter.
Column1
----------
8,12,13,14
10,2,3,4,5,6,7
Any help is really appreciated..
prabakaran.v
Re: Records in sequenctial order...
If you are in DS 8.5, you can use the TFM/LOOP to achieve this! Again this need to be implemented using Datastage Stage Variables.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Sura - I'm curious as to how you would solve this problem using a loop in the transform stage. Could you expand on your solution?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Kind of new to loopers but I would attempt something like this:
1. Use stage variables to work out how many 'loops' you have to do. (in this instance that would be EndNumber-StartNumber+1) Should probably also set some stage variables which hold you start and end numbers.
2. In the loop variables are - your loop while would be @ITERATION<=Stage Variable loop count
3. Create a loop variable(s) for your output. This would need to be a value which is added to each time an iteration happens, each time appending a delimiter and the next number in the sequence. As per the loop while, this will happen until the iteration equals your calculated loop count.
4. On your output link you can either output everything and have a remove duplicates stage after, or just put a constraint to output only when @ITERATION=loop count.
Or something similar. Good luck
1. Use stage variables to work out how many 'loops' you have to do. (in this instance that would be EndNumber-StartNumber+1) Should probably also set some stage variables which hold you start and end numbers.
2. In the loop variables are - your loop while would be @ITERATION<=Stage Variable loop count
3. Create a loop variable(s) for your output. This would need to be a value which is added to each time an iteration happens, each time appending a delimiter and the next number in the sequence. As per the loop while, this will happen until the iteration equals your calculated loop count.
4. On your output link you can either output everything and have a remove duplicates stage after, or just put a constraint to output only when @ITERATION=loop count.
Or something similar. Good luck
Andrew, pie is easy. Π is an irrational number and never easy.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
... unless you live in Indiana, where the state legislature in 1897 considered a law decreeingthe value of pi to be 3, or 3.2, or 4, to make it easier for school children to learn.FranklinE wrote:Andrew, pie is easy. Π is an irrational number and never easy.
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.
You can achieve required output as below suggested by ShaneMuir
1)Stage variables
Field(DSLink2.col2,'-',2)-Field(DSLink2.col2,'-',1)+1-Sv1
2)Loop condition-@ITERATION<= sv1
Output Derivations
3)col1 is direct mapping from Source
Field(DSLink2.col2,'-',1)+ @ITERATION-1-col2
then you will get following output
10,2
10,3
10,4
10,5
10,6
10,7
8,12
8,13
8,14
4)Then Use sort stage and Transfomerstage to identify the each group and also to perform the concatenation
1)Stage variables
Field(DSLink2.col2,'-',2)-Field(DSLink2.col2,'-',1)+1-Sv1
2)Loop condition-@ITERATION<= sv1
Output Derivations
3)col1 is direct mapping from Source
Field(DSLink2.col2,'-',1)+ @ITERATION-1-col2
then you will get following output
10,2
10,3
10,4
10,5
10,6
10,7
8,12
8,13
8,14
4)Then Use sort stage and Transfomerstage to identify the each group and also to perform the concatenation
@Vamsi.4a6 - nice start
For the sake of completeness I would add a key change stage variable, so its known where to reset the count, and I would also add stage variables to hold the start and end numbers rather than repeatedly perform the field calculation.
Its also possible to output only the last complete already concatentated row. In the loop variable itself the concatentated string can be built during the loop iterations, and then the output constraint for the row can be set to only output when the loop reaches the target number (which as been stored as an initial stage variable).
For the sake of completeness I would add a key change stage variable, so its known where to reset the count, and I would also add stage variables to hold the start and end numbers rather than repeatedly perform the field calculation.
Its also possible to output only the last complete already concatentated row. In the loop variable itself the concatentated string can be built during the loop iterations, and then the output constraint for the row can be set to only output when the loop reaches the target number (which as been stored as an initial stage variable).