Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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 RefAction DescriptionAction Date
AREFUND01/03/2013
BSWAP02/03/2013
AREFUND05/03/2013
BREFUND08/03/2013
ACANCEL06/03/2013
BSWAP08/03/2013
ACANCEL12/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 RefAction DescriptionAction Date
A05/03/2013
A12/03/2013
BREFUND08/03/2013
BSWAP08/03/2013

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

Many thanks

1 Solution

Accepted Solutions
Highlighted
sadickbasha
New Contributor III

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

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

3 Replies
Not applicable

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

Transaction RefAction RefLast Action Date
AREFUND05/03/2013
ACANCEL12/03/2013
BREFUND08/03/2013
BSWAP08/03/2013

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

Highlighted
sadickbasha
New Contributor III

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

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

Not applicable

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

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

Many, many thanks!