Generate numeric duplicate number column

jlitherl · Post by **jlitherl** » Fri Feb 09, 2007 10:02 am

I've imported data from a flat file into a dataset with a compound key based upon 5 columns, which can contain duplicates.

I've added a column which I plan to use to uniquely identify each of the duplicates, with values 1,2,3...

Can anyone tell me how to generate the numeric value in column 6 within DataStage, please?

Thanks

John

DSguru2B · Post by **DSguru2B** » Fri Feb 09, 2007 10:09 am

You can do this with stage variables. Use hash partition to ensure that similar keys end up in one partition. Use the stage variables to compare present row with previous row and a increment another stage variable which will be used as your counter. Reset it back if there is a change.
You need to take into account the partition number and number of partitions. If you dont want to go through all that. Run in sequential mode.
Sort your incoming data on your composite key.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 09, 2007 3:37 pm

If you use a Sort stage, it can generate a key change column. If this is 1, you have a new key; if this is 0 you have a duplicate.

jlitherl · Post by **jlitherl** » Tue Feb 13, 2007 12:00 pm

DSguru2B wrote:You can do this with stage variables. Use hash partition to ensure that similar keys end up in one partition. Use the stage variables to compare present row with previous row and a increment another stage variable which will be used as your counter. Reset it back if there is a change.
You need to take into account the partition number and number of partitions. If you dont want to go through all that. Run in sequential mode.
Sort your incoming data on your composite key.

Can I do this with a PX transformer stage? Someone suggested using the function RowProcGetPreviousValue() but I can only see this in the Server job transformer. How can I do the equivalent in PX?

Thanks for your help.

John

DSguru2B · Post by **DSguru2B** » Tue Feb 13, 2007 12:03 pm

With stage variables. Search this forum for more details on how to compare present row with previous using stage variables.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 13, 2007 2:50 pm

The Sort stage is far easier. It generates the "key is changed" value for you. All you have to do is to provide a column into which it can put this value. Any size of integer will do; the value is 0 or 1.

jlitherl · Post by **jlitherl** » Thu Feb 15, 2007 11:51 am

Alas, Ray, my company is too tight to pay for a premium membership to see most of your posting

But the stage variables alternative was straightforward enough. I put a zero in the extra column by default and increment this value each time the other key columns are repeated. Easy!

Thank you both very much for your help.

Regards

JohnL