count for no of rows

[murali]

Hi
I have scenario where i need to get number of rows for the same composite primary key
the mapping goes like this

Code: Select all

          

ODBC1--->TRANSFORMER---->ODBC2---->HASHFILE1
                       ^
                       |
                       |lkp
                       |
                    HASHFILE1 
Iam having 3 keys in my target odbc2  seen1 | sean2 |  jrnl_lines

seen1 and seen 2 comes from the source odbc1 and jrnl_lines is not comming from the source .
for  jrnl_lines i need to populate with  number of  times the same composite key is repeating 
for example  if for the first time  seen1 and sean2 are like  a1 and a2 the  the value fro jrnl _lines must be 1  and if the same record comes again from the source as a1 and a2 the value must be 2 for jrnl lines
i must get count for it 
is there any way that i can code for this    can any one help me  

[Sunshine2323]

Hi murali,

Use the Aggregator stage Grouping on the keys and do a Count on any one of the key.
You can use the aggregator stage after the transformer.
This will give you the number of times the composite key has appeared in the source odbc1.

Hope this helps

[kumar_s]

Hi,

Try using the option Key Change Column to enable in the Sort stage.
This servers you purpose. If you need to find the key change for 2 out of 3 key columns, first sort based on all the 3 columns and add another sort stage for Key change purpose with just 2 columns and can also mention Not to sort (Previously sorted) to not to degrade the performance.

-Kumar

[chulett]

Kumar, that would be a PX answer and not at all applicable to a Server job.

[kumar_s]

No Key change option in server job is it

-Kumar

[vijayindukuri]

Hin sunshine2323
i've tried ur method but its is not working ,u asked me to place aaggregator stage before transformer but i need the count for target resotds which have the same key
for example

Code: Select all

   
---------------------------------------------------
              A1     |   A2    |  TRG _LINES   
----------------------------------------------------
               AL        A23          1
               AL        A23          2
                BL       C23          1
                AL       C43          1
                AL        A23         3

THIS IS THE OUT PUT I NEED TO GET  EVEREY TIME I MUST GET THE MAXIMUM OF THE VALUE FOR REPEATED [b]A1 AND A2 [/b]WHICH ARE COMMING FROM SOURCE  AND [b]TRG_LINES [/b]MUST BE POPULATED .

A1 AND A2 ARE KEYS  COMMING FROM SOURCE AND  TRG_LINES IS A KEY IN THE TAREGT TABLE .

CAN ANY ONE HELP ME IN BUILDING THE LOGIC FOR THIS TAHNKUYOU VIJAY

[Sunshine2323]

Hi Vijay,

Your previous post gave me the impression that you wanted the total count of the number of times the composite key is repeating.

You can implement the logic for your last post by using a transform RowProcCompareWithPreviousValueand a sort stage

Code: Select all

Target---->Sort---->Transformer---->Target Stage1

Sort the data from your target stage on the Key A1 and A2A1 ASC,A2 ASC

Declare 3 stage variables:
StgColforCompaison=Target.A1:Target.A2
StgCompare=RowProcCompareWithPreviousValue(StgColforCompaison)
StgCount=if StgCompare=1 then StgCount+1 else 1

Transformer Derivation:
A1=Target.A1
A2=Target.A2
TRG_LINES=StgCount

This will give you the required output.

Code: Select all

A1,  A2,Trg_Lines
1110,1111,1
1110,1111,2
1110,1111,3
1110,1112,1
1111,1112,1

Hope this helps

[vijayindukuri]

Hi sunshine thankyou your solution is working very good and it helped me a lot .