Loop in DS (transformer)

[Luk]

Hello

I have following situation:

I've 4 rows in table in my source DB
x , x
y , y
z , z
s , s
, and I want to put to my target table sth. like this:

x , x , 1
x , x , 2
x , x , 3
y , y , 1
y , y , 2
y , y , 3
..........
s , s , 1
s , s , 2
s , s , 3

How can I do a loop in transformer stage and is it possible??

regards

[Sainath.Srinivasan]

Simplest will be to have 3 output links with each populating values 1, 2, 3 respectively.

[alhamilton]

For this specific task, the three output stages is a good solution, probably the best.

Another alternative is to use the multi-row lookup. I went so far as to create a table called process_counter. It had one column that was a number and I populated it with 1 - 1000. If you set this up once, you can reuse it again and again. The multi-row lookup yields multiple output records per input record.

[amsh76]

Try using PIVOT stage...

[Luk]

Try using PIVOT stage...

how can I use it?? I have no idea.

[Sunshine2323]

Hey I have written a job control that can easily do this task.

OPENSEQ "/home/dsadm/Input.txt" TO INPUT1 THEN
Call DSLogInfo("File Input.txt Opened Successfully.","Generating a Different Sequence")
END
ELSE
ErrorCode = 1
Call DSLogInfo("Error in Openening Input.txt. Aborting the job.","Generating a Different Sequence")
ABORT
END

OPENSEQ "/home/dsadm/Output.txt" TO OUTPUT THEN
Call DSLogInfo("File Output.txt Opened Successfully.","Generating a Different Sequence")
END
ELSE
ErrorCode = 1
Call DSLogInfo("Error in Openening Output.txt. Aborting the job.","Generating a Different Sequence")

END
LOOP
READSEQ INPUT1.RECORD FROM INPUT1 THEN

Call DSLogInfo("File is Being Read","Generating a Different Sequence")

F1= FIELD(INPUT1.RECORD,'|',1)
F2= FIELD(INPUT1.RECORD,'|',2)

for i=1 to 3

row<1>=F1
row<2>=F2
row<3>=i
CONVERT @AM TO "|" IN row
WRITESEQ row TO OUTPUT THEN
**Call DSLogInfo("File is Being Written.","Generating a Different Sequence")
END
next i
END
ELSE
Call DSLogInfo("CANNOT READ FILE","Generating a Different Sequence")
EXIT
END


REPEAT

CLOSESEQ INPUT1
CLOSESEQ OUTPUT

Hope this helps

[Prashantoncyber]

Its really nice way of stealing the points

[Prashantoncyber]

Luk, you dont have to bother with complex solutions

here its simple and efficient one by using Pivot stage as suggested by amsh76

Field1 Field2
x x
y y
z z
s s

take this input in In pivot stage
use below derivatives for pivoting
Field1,Field1,Field2 at 1st column

this will generate out putlike this


x x
x x
x x
y y
y y
y y
z z
z z
z z
s s
s s
s s

now use tranformer to create third column

Declare Stage Variable IncrSTG
give condition if incrSTG=3 then 1 else incrSTG+1 against this variable in derivative.

Now drag it to output column as 3rd col.

you will find out like this as desired.


x x 1
x x 2
x x 3
y y 1
y y 2
y y 3
z z 1
z z 2
z z 3
s s 1
s s 2
s s 3

I hope this will hepl .

I dedicate this solution to Sunshine2323

Thanks
Prashant

[ahmediftikhar]

Hi Prashant / Luk

What will happen if the data is like this:

x,a
y,b
z,c

Will i get the below result:

x,a,1
x,a,2
x,a,3
y,b,1
y,b,2
y,b,3
z,c,1
z,c,2
z,c,3

I guess Luk needs this than the above solution. Let me know.

Thanks
Ahmed.

[Prashantoncyber]

you are correct Ahmed let me lookinto this.

[Prashantoncyber]

Ahmed ,

Again its very simple

give this derivative in pivot output tab

Field001,Field001,Field001 at the 1st col

and u shall get what you want.

Thanks