Count(*)

[Nagaraj]

no the key column is coming from source so, based on that i will have more datasets containing counts for which stagvarible+1 combinations is not looking accurate.

[Nagaraj]

Source

col1 col2_key
Yes 1
Yes 1
No 1
No 1
Yes 2
Yes 2
NA 3
NA 4

hope this clarifies.

[arunkumarmm]

I'm sorry if I'm still missing something here.. To get the count, you dont need to pass your source key to the target hashed file. Your key, for example can be a hard coded '1' for all rows. For the sample data you gave the hashed file will look like:

Key YesCnt NoCnt NACnt
1 4 2 2

[Nagaraj]

I want count for that particular group and there are many groups....

how to deal with your solution if i have more groups?

[arunkumarmm]

Before the transformer sort the input rows with your key column. And create stage variables in the transformer like below:

svOld - svNew
svNew - InputColumn.Key
svCheck - If svOld <> svNew then 'Y' Else 'N'

sv1 - If svCheck = 'Y' Then If InputLink.Col1 = 'YES' Then 1 Else 0 Else If InputLink.Col1 = 'YES' Then sv1+1 Else sv1

sv2 - If svCheck = 'Y' Then If InputLink.Col1 = 'NO' Then 1 Else 0 Else If InputLink.Col1 = 'NO' Then sv2+1 Else sv2

sv3 - If svCheck = 'Y' Then If InputLink.Col1 = 'NA' Then 1 Else 0 Else If InputLink.Col1 = 'NA' Then sv3+1 Else sv3


And include your source key column in hashed file as key, then you will get counts for each group. I did not try this but it should work. Try this and let me know.

[Nagaraj]

Thanks Arun, i thought u will not check this thread, definately i will check this....

actually i did something like this already....may be i think i shd tweak my if then else more to get more accurate results.

will post the results soon.

[arunkumarmm]

Did it work?

[Nagaraj]

Yes it works now. thanks have a gr8 day