topic Re: Max Payment Date in QlikView

Max Payment Date

Mon, 26 Sep 2016 02:48:45 GMT

Hi,

how to show Transaction ID based on Max Payment Date.

Orginal Data:

Customer ID	Transaction No	Payment Date
csx01234d	snzcret34	7/15/2016
csx01234d	ynzcret35	7/16/2016
csx01234d	knzcre136	7/17/2016
csx23495	refrgmp532	7/18/2016
csx23495	tefrgmp533	7/19/2016
csx23495	hefrgmp534	7/20/2016
csx23458	kloiedftp13	7/21/2016
csx29458	ploiedftp14	7/22/2016
csx29458	zloiedftp15	7/23/2016
csx29460	lopredts24	7/24/2016
csx29460	fopredts25	7/25/2016
csx38570	sodbdu56	7/26/2016
csx38570	nodbdu57	7/27/2016
csx38570	modbdu58	7/28/2016

Output

Customer ID	Transaction No	Max Payment Date
csx01234d	knzcre136	7/17/2016
csx23495	hefrgmp534	7/20/2016
csx29458	zloiedftp15	7/23/2016
csx29460	fopredts25	7/25/2016
csx38570	modbdu58	7/28/2016

Re: Max Payment Date

tamilarasu — Mon, 26 Sep 2016 03:05:16 GMT

Hi,

Data:
LOAD * INLINE [
Customer ID, Transaction No, Payment Date
csx01234d, snzcret34, 7/15/2016
csx01234d, ynzcret35, 7/16/2016
csx01234d, knzcre136, 7/17/2016
csx23495, refrgmp532, 7/18/2016
csx23495, tefrgmp533, 7/19/2016
csx23495, hefrgmp534, 7/20/2016
csx23458, kloiedftp13, 7/21/2016
csx29458, ploiedftp14, 7/22/2016
csx29458, zloiedftp15, 7/23/2016
csx29460, lopredts24, 7/24/2016
csx29460, fopredts25, 7/25/2016
csx38570, sodbdu56, 7/26/2016
csx38570, nodbdu57, 7/27/2016
csx38570, modbdu58, 7/28/2016
];

Inner Join (Data)
LOAD [Customer ID],
Max([Payment Date]) as [Payment Date]
Resident Data Group by [Customer ID];

Note: Updated my solution.

Re: Max Payment Date

tresB — Mon, 26 Sep 2016 04:16:53 GMT

Straight table:

Dimension : [Customer ID]

Expression 1 : FirstSortedValue([Transaction No], - [Payment Date])

Expression 2 : Date(Max([Payment Date]))

Re: Max Payment Date

Digvijay_Singh — Mon, 26 Sep 2016 04:23:00 GMT

May be like this

Re: Max Payment Date

tresB — Mon, 26 Sep 2016 04:29:19 GMT

FirstSortedValue([Payment Date], - [Payment Date]) would be an overkill when the same can come directly from Max([Payment Date]), I guess.

Re: Max Payment Date

Digvijay_Singh — Mon, 26 Sep 2016 04:35:03 GMT

Agree, Thanks for correcting!

Re: Max Payment Date

nikhilpotdar — Mon, 26 Sep 2016 05:31:59 GMT

My Solution:

Straight table:

Dimension 1: [Customer ID]

Dimension 2 : [Transaction No]

Expression 1 : aggr(Max([Payment Date]),[Customer ID])

Re: Max Payment Date

soloeeeoff — Mon, 26 Sep 2016 06:23:18 GMT

Please Try like this in chart

=max({1}[Payment Date])

=FirstSortedValue({1}TOTAL <[Customer ID]> [Transaction No],-[Payment Date])

In first sorted value, please note 'Minus Sign'

Thanks,

From Solomon Jeyaseelan