DSXchange

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.

Source

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

hope this clarifies.

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

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

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

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.

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.

Did it work?

Yes it works now. thanks have a gr8 day