Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
darren_dixon
New 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
Esteemed Contributor III

Re: Max Date - Expression or Script?

try

firstsortedvalue(Transactiondate,-Value)

Partner
Partner

Re: Max Date - Expression or Script?

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)

atkinsow
Valued Contributor II

Re: Max Date - Expression or Script?

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

Re: Max Date - Expression or Script?

May be this