ETL logic using server jobs

[kirankumarreddydesireddy]

Hi

We have text file as source,it contains only one column.The first row is related to job status of second row which is jobname, and third row is related to job status of fourth row which is Jobname and so on.....

The output should contain 2 columns i.e job status and that particular jobname.(it is guaranteed that jobstatus and job name are in respective sequential order only)


eg :

Input :

COLUMN
-------
RUN OK
test_seq
WARNING
test_seq1
FAILED
testseq2

Output :

Jobstatus Jobname
-----------------------
RUN OK test_seq
WARNING test_seq1
FAILED test_seq3

We are using datastage server jobs.(7.5v) on Windows server.

Solutions we had tried :

We had splitted the single source link into two links using Transformer constraints as Mod(@inrownum,2) = 0 into one link(it contains data as

test_seq
test_seq1
test_seq2

and the Mod(@inrownum,2) <> 0 into second link.

RUN OK
WARNING
FAILED

We are not sure how to combine these two links to get my desired output.

i.e

Output :

JObstatus Jobname
-----------------------
RUN OK test_seq
WARNING test_seq1
FAILED test_seq3


Can anyone help me on this.


Thanks
Kiran

[ankursaxena.2003]

I think Vertical Pivot should help.

Let's say that you have 6 rows with data and then it will be repeating.
So, you need to concate first 6 rows in 1st record and then next 6 rows in 2nd record and so on.

Afterwards you can parse them out and put it in different columns.

[Kryt0n]

You can do it the way you specified but would need to add a "key" which could be the DIV by two.

Alternatively, you keep keep every odd row in a Stage Variable and output on every even row

[reddyamarnath]

Below is the design to do your logic.

Seqfile-------->Transformer------->output

In the transformer you declare stage variable as below

StgVar---If Mod(@INROWNUM,2)=1 Then InputColumn Else StgVar: '|' : InputColumn

In the constraint give the condition as -- Mod(@INROWNUM,2)=0

In the derivation for output column pass the Stagevarible i.e StgVar

Then you will get the require ouput.

[kirankumarreddydesireddy]

Thanks.The solution you provided looks pretty simple and good.

We had implemented this,in other way as well.

We had splitted the single source link into two links using Transformer constraints as Mod(@inrownum,2) = 0 into one link(it contains data as

test_seq
test_seq1
test_seq2

and the Mod(@inrownum,2) <> 0 into second link.

RUN OK
WARNING
FAILED


We had included one more column, which will populate @outrownum and the result of first link will be

1 test_seq
2 test_seq1
3 test_seq2


and the result of second link will be

1 RUN OK
2 WARNING
3 FAILED


We had loaded them into 2 separate hash files with @outrownum as key.


In the next job we had done look up on one hash file to other hash file with @outrownum as key value,and as the result of keys matching,we got the desired output

JObstatus Jobname
------------------
RUN OK test_seq
WARNING test_seq1
FAILED test_seq3

Thanks
Kiran