How to handle this logic?

[v2kmadhav]

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

[chandra.shekhar@tcs.com]

Madhav, Thanks for your effort.

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

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.

[v2kmadhav]

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

[Kryt0n]

I have done hash partioning with ACCT_NO asc and REC_NO desc.

this goes against what you said you were doing in response to my comment. partition on ACCT_NO ONLY, sort on REC_NO but do not partition. Read up on partitioning for an explanation why

[chandra.shekhar@tcs.com]

@krypton
ACCT_NO and REC_NO are part of primary key. So in the transformer how can I explicitly mention hash partitioning on ACCT_NO only.

[v2kmadhav]

Chandra

ACC_NO - Partitioning, Sorting (Asc)
REC_NO - Sorting (Asc)

Is what you need to have before your xfm stage.
This would certainly work for your requirement.

[chandra.shekhar@tcs.com]

Yeah Yeah.... I got it now.
That was a silly thing which I forgot.
Now I can run my job on multiple nodes and the logic is also working fine.
Thanks guys for your efforts.