Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
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.

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

Highlighted
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!

Community Browser