Handling a duplicate max date

[mellerbeck]

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!

[mellerbeck]

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;

[mellerbeck]

No, darn

[vishsaggi]

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;

[vvira1316]

You can use FirstSortedValue in Chart not in script

[mellerbeck]

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

[mellerbeck]

firstsortedvalue does work in the script

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/AggregationFun...

[sunny_talwar · Accepted solution]

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;

[vvira1316]

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

[mellerbeck]

Thanks!