stage variables:
svChk (If (svPresKey <> svPrevKey) Or (svPresKey = svPrevKey And svRecDiff<>1) Then 0 Else 1)
svDescCon (If svChk =0 Then Trim(DSLink5.DESC) Else Trim(DSLink5.DESC) : svDescCon)
svNewRecNo (If svChk = 0 Then DSLink5.REC_NO Else svNewRecNo)
OutputLink1 - main : when svChk = 0
ACC_NO, REC_NO, DESC
OutputLink2 - for reference : no constraint
ACC_NO, REC_NO, REC_NO_NEW (svNewRecNo), DESC (svDescCon)
remove duplicate stage on this reference stream: kyes: ACC_NO, REC_NO_NEW (hash on ACC_NO, REC_NO_NEW, REC_NO(only for sorting))
then lookup into this stream with keys as ACC_NO and REC_NO_NEW.
Let me know if this is not clear enough..
How to handle this logic?
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
Madhav, Thanks for your effort.
This is what I have done.
I have done hash partioning with ACCT_NO asc and REC_NO desc. The transformer has two outputs. One output goes to aggregator checking the max count there. And then it joins with the 2nd output on ACCT_NO and COUNT columns.
The problem is that in the transformer the logic works correctly but for some records only!!
I am using 8 nodes, I wonder what is happening inside the transformer?
Please let me know if I am missing something.
This is what I have done.
Code: Select all
svOldAcc ------ ACCT_NO
svNewRec--------REC_NO
svCount----------If (svOldAcc = svNewAcc and svOldRec = svNewRec -1) then svCount + 1 else 0
svDscrpt---------If (svOldAcc = svNewAcc and svOldRec = svNewRec -1) then svDscrpt : ' ' : trim(Descript) else trim(Descript)
svNewAcc--------svOldAcc
svNewRec--------svOldRec
The problem is that in the transformer the logic works correctly but for some records only!!
I am using 8 nodes, I wonder what is happening inside the transformer?
Please let me know if I am missing something.
Thanx and Regards,
ETL User
ETL User
I would try running the job sequentially to ensure that you have the stage variable logic right first before being worried about the partitioning.. (i know not ideal)
But you could get one issue out of the way..
You could still Hash it before the xfm on ACC_NO, REC_NO usage as sort only... That will ensure your related accounts stay together...
But you could get one issue out of the way..
You could still Hash it before the xfm on ACC_NO, REC_NO usage as sort only... That will ensure your related accounts stay together...
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India