How do I include only certain rows in a load script?

[#1]

Hi all,

I'm new to Qlikview scripting and have found the forum invaluable for helping me overcome problems but I can't find an answer to this one. I have an input table that looks similar to this:-

Transaction Ref	Action Description	Action Date
A	REFUND	01/03/2013
B	SWAP	02/03/2013
A	REFUND	05/03/2013
B	REFUND	08/03/2013
A	CANCEL	06/03/2013
B	SWAP	08/03/2013
A	CANCEL	12/03/2013

I would like to load only the most recent date for each unique combination of Transaction Ref & Action Description so that the table loaded looks like this:-

REFUNDCANCEL

Transaction Ref	Action Description	Action Date
A	05/03/2013
A	12/03/2013
B	REFUND	08/03/2013
B	SWAP	08/03/2013

Any help or hints on how I should script this would be really appreciated.

Many thanks

[#2]

Transaction Ref	Action Ref	Last Action Date
A	REFUND	05/03/2013
A	CANCEL	12/03/2013
B	REFUND	08/03/2013
B	SWAP	08/03/2013

Sorry - Gremlins in my table. The output should be as above.

[sadickbasha · Accepted solution]

Hi Vivienne,

below script will help you resolve the issue

test:
LOAD [Transaction Ref],
     [Action Description],
num([Action Date]) as [Action Date]
FROM
..\test.xlsx
(ooxml, embedded labels, table is Sheet3);


NoConcatenate

LOAD
[Transaction Ref],
[Action Description],
Max([Action Date]) as Action_Date
REsident test
Group by
[Transaction Ref],
[Action Description];
 
drop table test;

After reloaded the application , the Action_Date field to be converted to Date(Action_Date,'M/D/YYYY')

by

Sadick

[#4]

So straightforward. I was overcomplicating things looking at things like FirstSortedValue().

Many, many thanks!