How to reject older version of records

sathyak098 · Post by **sathyak098** » Tue Sep 10, 2013 4:59 am

Hi,

My input is like below.

Name,Version_No,Timestamp,detail
AAA,03,9/10/2013 03:51:57,qwer
AAA,02,9/10/2013 02:40:17,rfgt
AAA,04,9/10/2013 03:55:40,asdf1
BBB,01,9/9/2013 03:25:57,uyhj0
BBB,02,9/9/2013 03:51:45,wert
CCC,02,9/9/2013 02:30:50,uyhj12
CCC,03,9/9/2013 02:35:40,uyhj23

I need to process the only the records which has maximun version no and timestamp.
I did use create key change of sort stage based on key 'Name'. However,
It does work for only a few data.
Can I get any input?

priyadarshikunal · Post by **priyadarshikunal** » Tue Sep 10, 2013 5:14 am

I have to admit I did not get what you are trying to say. What problem you got while using create key change column?

Did you ensure it is correctly partitioned and sorted?

ArndW · Post by **ArndW** » Tue Sep 10, 2013 5:16 am

If you sort your input by Name, then by desceding Version_No, then by descending Timestamp thenyour first record in each group will be the one you wish to keep and you could set your sort stage to not allow duplicates and get your desired result.

sathyak098 · Post by **sathyak098** » Tue Sep 10, 2013 5:32 am

My job design is
Table -----Sort-----File

While extracting, in a query I added name in ascending, version and timestamp in descending.

in sort stage, I applied hash partition and sort on 'Name'. And gave key as 'Name'.

For a few records I got '1' for first record (which are max of version & timestamp). For a few, i got '0', which are max of ver and timestamp.

sathyak098 · Post by **sathyak098** » Tue Sep 10, 2013 5:35 am

It is taking combination all 3 fields as a group. So for all the records I'm getting create key value '1'

ArndW · Post by **ArndW** » Tue Sep 10, 2013 6:19 am

If you go back to your sample data, can you have multiple records with identical "Name" and "Version" columns but with different "Timstamp" values?

If you use the sort as you've defined, with the "Name" column used to partition the data then add a remove duplicates stage using "Name" and "Version" as the key and keep the first record. If a given "Version" contains more than one timestamp, then the prior sort descending ensures that only the one with the highest value is kept.

sathyak098 · Post by **sathyak098** » Tue Sep 10, 2013 6:34 am

No. I will get duplicates based on 'Name'. I will not get duplication combination of 2 fields(name and version (or) name and timestamp)

ArndW · Post by **ArndW** » Tue Sep 10, 2013 6:37 am

Sorry, just duplicate on the single key "Name". If the data is sorted correctly you will get one record per "Name" containing the most recent, highest numbered "Version".

priyadarshikunal · Post by **priyadarshikunal** » Tue Sep 10, 2013 8:20 am

Seems your partitioning/sort order is messed up during transfer from table to sort stage.

Can you try sorting and then remove duplicate keeping same partitioning between sort and remove duplicate. Keep first or last based on your sort order?

Or a fork join will also work. These will have some overhead but just to try if it relates to data or partitioning/sorting.

By the way which DataStage version you have there.

sathyak098 · Post by **sathyak098** » Thu Sep 12, 2013 3:41 am

Hi,
Im using 8.5.
and, I do need to send older version of records to rejections which will be used by other process, hence I can not apply remove duplicates.

So, in this situation, do I need to use double sort stages?

priyadarshikunal · Post by **priyadarshikunal** » Thu Sep 12, 2013 4:13 am

Try with two sort stage or try running on one node to check if it works fine.