topic Re: How do I include only certain rows in a load script? in QlikView

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

Tue, 05 Nov 2013 09:36:45 GMT

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

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

Tue, 05 Nov 2013 09:40:23 GMT

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.

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

sadickbasha — Tue, 05 Nov 2013 10:03:40 GMT

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')

Sadick

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

Tue, 05 Nov 2013 10:27:11 GMT

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

Many, many thanks!