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
ETL logic using server jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 110
- Joined: Mon Jan 11, 2010 4:22 am
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
-
- Participant
- Posts: 8
- Joined: Thu Jun 17, 2010 1:12 am
Re: ETL logic using server jobs
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.
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.
reddy,amarnath
-
- Participant
- Posts: 110
- Joined: Mon Jan 11, 2010 4:22 am
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
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