topic Re: Get last transaction attributes issue with FirstSortedValue in QlikView

Get last transaction attributes issue with FirstSortedValue

LoKi_asterix — Thu, 26 Feb 2026 10:36:56 GMT

Hey everyone, I’m running with this syntax, but I’ve run into a snag. A few records share the same date time, which leaves their Ref, Sales, and Narration fields showing up as null. The system does generate a Recid in the format 177980236362379.000001, but the tricky part is that the Recid values aren’t consistent one of them happens to be 177980236362379.000001. Is there another way around this? And if not, how can I make better use of the Recid field

Last_Transactions:
LOAD CustID,
Customer,
FirstSortedValue(Ref, -[DATE TIME]) as Ref,
FirstSortedValue(Sales, -[DATE TIME]) as Sales,
FirstSortedValue(Narration, -[DATE TIME]) as Narration,
Max([DATE TIME]) as [DATE TIME],
Max([Value Date]) as [Value Date]
RESIDENT Temp_Transactions
GROUP BY CustID, Customer;

Re: Get last transaction attributes issue with FirstSortedValue

Or — Thu, 26 Feb 2026 14:08:01 GMT

I tried wrapping my head around the question, but couldn't quite follow. If you have an ID column, why not use that for your FirstSorted sort?

Re: Get last transaction attributes issue with FirstSortedValue

LoKi_asterix — Fri, 27 Feb 2026 09:25:42 GMT

After presorting the dataset, I applied FirstValue(), and it returned the expected result.

Re: Get last transaction attributes issue with FirstSortedValue

marcus_sommer — Fri, 27 Feb 2026 09:26:34 GMT

The aggregation might be outsourced - maybe in this way:

Last_Transactions:
LOAD CustID,
Customer,
Ref,
Sales,
Narration,
[DATE TIME]
RESIDENT Temp_Transactions;

inner join(Last_Transactions)
LOAD CustID,
Customer,
Max([DATE TIME]) as [DATE TIME],
Max([Value Date]) as [Value Date]
RESIDENT Temp_Transactions
GROUP BY CustID, Customer;

Another approach could be to use interrecord-functions like peek() and previous() within an appropriated sorted resident-load and comparing the values from the current record against the previous ones and then flagging the records. It avoids to handle (n) aggregation tables and provides extra possibilities in regard to calculate offsets between the records and/or to accumulate them.