DSXchange

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

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.

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?

Given the arbitrary number of integers in the range I'd rule out using stage variables, but would definitely rule IN loop variables in the Transformer stage.

Definetly Loop variables. delimit stage variable by ,. if field has - then loop from first to send (delimit again by -). Just a first blush guess.

Easy as pi.

Thanks for your reply aarlett. Could you please bit explain the logic ?

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

Andrew, pie is easy. Π is an irrational number and never easy.

FranklinE wrote:Andrew, pie is easy. Π is an irrational number and never easy.

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

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

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

Thanks to everyone. I fixed my problem.

Once again thx for your tremendous support :D