New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor III

Find a unique max date

Hi Qlikers,

I have a table and based on several conditions I want to find a unique date for each ID.

The table is:

 ID NUM NAME DATE 1 10 John 01/03/2016 1 20 Mike 12/03/2016 1 30 Tom 14/03/2016 2 10 John 04/03/2016 2 20 Tom 10/03/2016 2 30 Mike 10/03/2016 2 40 David 06/03/2016

So basicaly the first think to do is to find the max DATE for each ID. For some ID you can have several lines with the same max DATE. In this case we look for the max NUM.

The expected result is:

 ID NUM NAME DATE 1 30 Tom 14/03/2016 2 30 Mike 10/03/2016

I tried a few things with GROUP BY, but the reultisn't there yet.

Tags (2)
1 Solution

Accepted Solutions
MVP

Re: Find a unique max date

May be this:

Table:

NUM,

NAME,

DATE

FROM

(html, codepage is 1252, embedded labels, table is @1);

Right Join(Table)

Max(DATE) as DATE

Resident Table

Group By ID;

Right Join (Table)

Max(NUM) as NUM

Resident Table

Group By ID;

15 Replies
MVP

Re: Find a unique max date

May be this:

Table:

NUM,

NAME,

DATE

FROM

(html, codepage is 1252, embedded labels, table is @1);

Right Join(Table)

Max(DATE) as DATE

Resident Table

Group By ID;

Right Join (Table)

Max(NUM) as NUM

Resident Table

Group By ID;

MVP

Re: Find a unique max date

On front end,

Create the straight table

Dimension:

ID

Expressions:

1) Date-

FirstSortedValue (distinct Date,-Date)

2) Name-

FirstSortedValue (distinct only({<Num={"\$(=max(Num)"}>}Name),-Date)

3) Num -

FirstSortedValue (distinct Sum({<Num={"\$(=max(Num)"}>}Num),-Date)

MVP

Re: Find a unique max date

I would use these expressions for 2 and 3

2) FirstSortedValue (NAME,-(DATE*10000+NUM))

3) FirstSortedValue (NUM,-(DATE*10000+NUM))

Honored Contributor II

Re: Find a unique max date

Hi Xavier,

Doing this, you will be able to fetch the max date for every unique ID

Check this out:

FirstSortedValue (distinct DATE,-DATE) resident ........;

Thanks,

Sangram Reddy.

MVP

Re: Find a unique max date

Can you explain how it works? sunindia

MVP

Re: Find a unique max date

Sure.

Same idea of trying to find the max (or min) based on another field, but in this case multiple fields. So to combine the multiple fields sorting, you can add them. Look at the image below

From just DATE we have a tie for ID 2 for NUM 20 and 30. But if I combine these two DATE*10000+NUM (More weight to the DATE and hence times 10000). Now the new number we just created will pick the right max based on DATE and NUM.

Does that make sense?

MVP

Re: Find a unique max date

ek number bhai sunindia

MVP

MVP

Re: Find a unique max date

Is it good w.r.t performance?