Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:-
Transaction Ref | Action Description | Action Date |
---|---|---|
A | REFUND05/03/2013 | |
A | CANCEL12/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
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
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.
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
So straightforward. I was overcomplicating things looking at things like FirstSortedValue().
Many, many thanks!