Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Returning only the most recent transaction associated with an ID

Hello,

I have a transaction log with a column containing IDs and a column containing dates. What is the most efficient way to return the most recent date for each ID (along with the other columns in the table of course). I believe I should be using firstsortedvalue() but I'm unclear on how exactly to use it and how it works.

right now my script looks like:

Table:

LOAD ID as %ID,

     Date as Date,

     Info as Info

From

(qvd);

Thanks

1 Solution

Accepted Solutions
Employee
Employee

Re: Returning only the most recent transaction associated with an ID

Just use one of the formulae in a text object or a chart (with any dimension)

=FirstSortedValue(Info,-Date)

=Concat(If(Date=Max(total Date), Info),',')

HIC

8 Replies
Employee
Employee

Re: Returning only the most recent transaction associated with an ID

If you use

FirstSortedValue(Info,-Date)

in a chart or text box in the UI, you should get the Info for the last date. Note the minus-sign before date.

HIC

Employee
Employee

Re: Returning only the most recent transaction associated with an ID

The above formula has a flaw... If there is more than one Info for the last Date, it will return NULL. Try the following formula instead:

     =Concat(If(Date=Max(total Date), Info),',')

HIC

Not applicable

Re: Returning only the most recent transaction associated with an ID

Can you please give me a little bit more detail regarding how exactly to implement this? I've tried putting it in as an expression for a list box that had Info as its field, but it didn't appear to do anything.

Employee
Employee

Re: Returning only the most recent transaction associated with an ID

Just use one of the formulae in a text object or a chart (with any dimension)

=FirstSortedValue(Info,-Date)

=Concat(If(Date=Max(total Date), Info),',')

HIC

Not applicable

Re: Returning only the most recent transaction associated with an ID

Ok, thank you, I've got it working on the sheet, however is there any way to have the system only import that information when I'm loading the data?

It wouldn't happen to be LOAD Concat(etc...) as Info would it?

calvindk
Contributor III

Re: Returning only the most recent transaction associated with an ID

Edit for brainbleed:

Do a group by id load first and take max date, then do an join load after. That will keep only the newest date.

Something like:

PreLoad:

Max(Date) as MaxDate,

Id // WILL JOIN ON Id

From XX

Group By Id;

JOIN (PreLoad)

Load

Date,

Id,

OtherStuff

From XX;

michael_solomon
New Contributor III

Re: Returning only the most recent transaction associated with an ID

Anders' method will work, but may load slowly if the table is large.  Another method is to load the data in an prdered way, and then compare the record to the previous record using the Previous function to only include the ID with the max date.

LoadOnlyLatest:

NoConcatenate

Load *

Resident OriginalLoad

WHERE ID <> Previous(ID)

ORDER BY ID, Date DESC;

ashwanin
Valued Contributor

Re: Returning only the most recent transaction associated with an ID

Date and ID can be shorted by setting in Chart or data table.

Open properties of data Sheet,  go to Short and change the frequency from z to a or a to z .

rgds

Ashwani

Community Browser