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: 
darren_dixon
Contributor III
Contributor III

Max Date - Expression or Script?

Hi,

How can I get the max Transaction Date, Payment Method and value from the following data? Can this be done as an expression or in the script?

Data:

Tenancy NumberTransaction DatePayment MethodValue
1000105/11/2017Cash£10.00
1000112/11/2017Direct Debit£20.00
1000119/11/2017Direct Debit£30.00
1000205/11/2017Direct Debit£40.00
1000212/11/2017Cash£50.00
1000312/11/2017Cash£60.00

Result:

Tenancy NumberTransaction DatePayment MethodValue
1000119/11/2017Direct Debit£30.00
1000212/11/2017Cash£50.00
1000312/11/2017Cash£60.00

Thanks,

Darren

4 Replies
Chanty4u
MVP
MVP

try

firstsortedvalue(Transactiondate,-Value)

avkeep01
Partner - Specialist
Partner - Specialist

Hi Darren,

In the script you could add the following lines:

LEFT JOIN (OriginalTableName) LOAD

[Tenancy Number],

DATE(MAX([Transaction Date])) AS [Transaction Date],

1 AS [Flag Last Transaction]

RESIDENT OriginalTableName

GROUP BY [Tenancy Number];

From there you have multiple solutions to work with:

- you could make a resident on the orginal table with a WHERE [Flag Last Transaction] = 1

- in the front end you could use SUM({$<[Flag Last Transaction]={1}>} Value)

Anonymous
Not applicable

If  you don't care about keeping the other records you could just do this in your script.

(Assuming [Transaction Date] is a date field not a string)

Data:

Load

[Tenancy Number],

[Transaction Date],

[Payment Method],

Calue

From YourTable

inner join(Data)

Load

[Tenancy Number],

max(Transaction Date] as [Transaction Date]

Resident Date

Group By [Tenancy Number];

antoniotiman
Master III
Master III

May be this