topic Re: Script to load the first sorted instance of a record in Scalability

Script to load the first sorted instance of a record

Anonymous — Wed, 14 Jan 2015 00:35:34 GMT

Hi guys,

I am trying to figure out a way to load the first instance of duplicated data in my script. I will then use this data to join to another set of data.

e.g,

Customer	Cummulative Sum	Date
a	50	28/12/14
a	40	20/12/14
a	30	17/12/14
a	20	15/12/14
a	10	01/12/14

So I want only one instance of customer a which would be the latest sorted date where sum would equal to 50 and data would be 28/12/14.

I think in Teradata, I would have the following at the end of my SQL

qualify rownumber() over( partition by customer, cummulative order by date)=1

Re: Script to load the first sorted instance of a record

msteedle — Wed, 14 Jan 2015 00:50:17 GMT

FirstSortedValue is an aggregation function you can use in the script to do just this, grouped by Customer, in your example. The example in the QV Desktop help for that function is very similar to what you are doing.

Re: Script to load the first sorted instance of a record

Anonymous — Wed, 14 Jan 2015 04:09:30 GMT

Thanks for the reply, so basically, I need to have this function at every column that I don't group with

Re: Script to load the first sorted instance of a record

msteedle — Wed, 14 Jan 2015 04:20:23 GMT

As with all aggregating in the script, any expression not in the GROUP BY requires some aggregation function.

An alternative would be breaking it into multiple steps, like loading the original table, then doing a resident load with ORDER BY and use an inter record functions like Peek to create an indicator or flag on the latest row per customer. Lastly you can do a resident load of that table WHERE the indicator or flag value is for the latest row, or just inner join an inline table containing only the desired flag or indicator value.

Re: Script to load the first sorted instance of a record

Anonymous — Wed, 14 Jan 2015 04:22:27 GMT

Make sense, will give this a shot, thanks