denormalize seqential file data

[rahul.kissu]

plz solve this
I have file with 2 fields
state ZIPCODE
fg 500050
sn 677887
kk 789879
fg 578687
fg 587887
sn 687688

i need a output like

state zipcode count
fg 500050,578687,587887 3
sn 677887,687688 2
kk 789879 1

[anbu]

Code: Select all

Sequential File --> Sort --> Transformer --> Remove Duplicates

Sort the data on state. In transformer store previous state and zipcode in stage variables and compare current row with previous stage variables. If the state is same append zipcode to current row and increment the count.
Use remove duplicates and sort ascending on state, count and retain last.

[rahul.kissu]

please explain transformer derivation in more details

[ray.wurlod]

Question: Sunil, are you marketing yourself as a DataStage practitioner?

The technique here is "vertical pivot". Details may be found if you
Search DSXchange.

If you want folks to provide complete solutions, chances are that they'll want to be paid. Because they, not you, will have done the consulting work.