Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
hic
Former Employee
Former Employee

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

View solution in original post

8 Replies
hic
Former Employee
Former Employee

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

hic
Former Employee
Former Employee

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
Author

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.

hic
Former Employee
Former Employee

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
Author

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
Creator III
Creator III

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
Partner - Contributor III
Partner - Contributor III

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
Specialist
Specialist

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