topic Re: Handling a duplicate max date in QlikView

Handling a duplicate max date

mellerbeck — Wed, 25 Nov 2020 16:16:04 GMT

Given

ACCOUNT, TERMS, EFFECTIVEDATE

1, NET 20, 2015-01-01

1, NET 40, 2015-01-01

2, NET 30, 2015-01-01

2, NET 45, 2016-01-01

Would like to have the max date of the account and term returned like below. Of course the duplicate effective date makes it interesting

ACCOUNT, TERMS, EFFECTIVEDATE

1, NET 40, 2015-01-01

2, NET 45, 2016-01-01

I tried

LOAD ACCOUNT,

FirstSortedValue (TERMS,EFFECTIVEDATE + rand()) as TERMS,

date(MAX(EFFECTIVEDATE)) as EFFECTIVEDATE

Resident DATA

Group by ACCOUNT;

Which breaks the tie, but it also alternates. I would like to break the tie in a repeatable manner, say using the maxstring of terms or something?

Thanks for any thoughts!

Re: Handling a duplicate max date

mellerbeck — Fri, 11 May 2018 19:14:41 GMT

Is it as simple as using the distinct with the FirstSortedValue ?

LOAD ACCOUNT,

FirstSortedValue (DISTINCT TERMS, EFFECTIVEDATE) as TERMS,

date(MAX(EFFECTIVEDATE)) as EFFECTIVEDATE

Resident DATA

Group by ACCOUNT;

Re: Handling a duplicate max date

mellerbeck — Fri, 11 May 2018 19:18:40 GMT

No, darn

Re: Handling a duplicate max date

vishsaggi — Fri, 11 May 2018 19:30:04 GMT

Can you try something like this?

DATA:

LOAD ACCOUNT, TERMS, SUBFIELD(TERMS, ' ') AS TERMNUM, DATE(EFFECTIVEDATE, 'YYYY-MM-DD') as EFFECTIVEDATE,

ACCOUNT&'|'&TERMS AS KeyAccnt INLINE [

ACCOUNT, TERMS, EFFECTIVEDATE

1, NET 20, 2015-01-01

1, NET 40, 2015-01-01

2, NET 30, 2015-01-01

2, NET 45, 2016-01-01

];

NoConcatenate

LOAD ACCOUNT,

Max(TERMNUM) AS TERMS,

MAX(EFFECTIVEDATE) AS EFFECTIVEDATE

Resident DATA

Group by ACCOUNT

;

drop table DATA;

OR Just use MaxString() like below:

DATA:

LOAD ACCOUNT, TERMS, DATE(EFFECTIVEDATE, 'YYYY-MM-DD') as EFFECTIVEDATE,

ACCOUNT&'|'&TERMS AS KeyAccnt INLINE [

ACCOUNT, TERMS, EFFECTIVEDATE

1, NET 20, 2015-01-01

1, NET 40, 2015-01-01

2, NET 30, 2015-01-01

2, NET 45, 2016-01-01

];

NoConcatenate

LOAD ACCOUNT,

MaxString(TERMS) AS TERMS,

MAX(EFFECTIVEDATE) AS EFFECTIVEDATE

Resident DATA

Group by ACCOUNT

;

drop table DATA;

Re: Handling a duplicate max date

vvira1316 — Fri, 11 May 2018 19:31:54 GMT

You can use FirstSortedValue in Chart not in script

Re: Handling a duplicate max date

mellerbeck — Fri, 11 May 2018 19:35:08 GMT

No, because then it will return the max of the TERMS for other entries as well

Re: Handling a duplicate max date

mellerbeck — Fri, 11 May 2018 19:36:15 GMT

firstsortedvalue does work in the script

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/AggregationFunctions/firstsorted.h…

Re: Handling a duplicate max date

sunny_talwar — Fri, 11 May 2018 19:44:49 GMT

One option is to do something like this

DATA:

LOAD ACCOUNT, TERMS, DATE(EFFECTIVEDATE, 'YYYY-MM-DD') as EFFECTIVEDATE,

DATE(EFFECTIVEDATE, 'YYYY-MM-DD')*100000 + RowNo() as KEY INLINE [

ACCOUNT, TERMS, EFFECTIVEDATE

1, NET 20, 2015-01-01

1, NET 40, 2015-01-01

2, NET 30, 2015-01-01

2, NET 45, 2016-01-01

];

Right Join (DATA)

LOAD ACCOUNT,

Max(KEY) as KEY

Resident DATA

Group By ACCOUNT;

Re: Handling a duplicate max date

vvira1316 — Fri, 11 May 2018 19:48:18 GMT

My bad, I tried checking help to make sure before I commented

Re: Handling a duplicate max date

mellerbeck — Fri, 11 May 2018 19:52:17 GMT

Thanks!