Returning only the most recent transaction associated with an ID

[#1]

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

[hic]

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]

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

[#4]

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 · Accepted solution]

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

[#6]

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]

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]

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]

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