Script to load the maximum date for an ID

[#1]

Hi

I want to be able to to load a table which has some IDs where each will have a date in an order of the month.

So something like:

ID	Sales	Date
1	100	01/01/2014
1	200	02/01/2014
1	300	03/01/2014
1	400	04/01/2014
2	200	01/01/2014
2	300	02/01/2014
2	400	03/01/2014
2	500	04/01/2014
3	700	01/01/2014
3	750	02/01/2014
3	900	03/01/2014
3	950	04/01/2014

I have a data source which updates daily but I only require the monthly value which is based on the most recent date from the above table. I have a historic QVD and I want to append my desired result to the QVD.

I want to achieve the following result written in a script.

ID	Sales	Date
1	400	04/01/2014
2	500	04/01/2014
3	950	04/01/2014

The above desired result has the latest sales figures for each month based on the most recent date going by each ID. If a new Sales amount arrives in the data for 05/01/2014, then I want the above values for 04/01/2014 to be removed and overwritten with the new values for 05/01/2014.

This technique is to have a Sales value for each Calendar month based on new Sales coming in on the new day in the month. I am not adding the previous sales values but I am just updating the QVD with the new Sales value which comes in for each new day in the month.

Is there a way I can achieve this?

[perumal_41 · Accepted solution]

Hi Rajesh

try below script

Load

ID ,FirstSortedValue(Sales,-Date) as Sales,FirstSortedValue(Date,-Date) as Date group by ID;

LOAD * INLINE [
ID Sales Date
1 100 01/01/2014
1 200 02/01/2014
1 300 03/01/2014
1 400 04/01/2014
2 200 01/01/2014
2 300 02/01/2014
2 400 03/01/2014
2 500 04/01/2014
3 700 01/01/2014
3 750 02/01/2014
3 900 03/01/2014
3 950 04/01/2014
]

(delimiter

is

' ');;

[#3]

Thank you for this.

This helped as I could add another field just in case I want to deduct a Sales value from Cost, let's say.