15 Replies Latest reply: Apr 4, 2016 4:17 AM by Xavier HEMELAAR

# 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.

• ###### 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;

• ###### Re: Find a unique max date

Hi Sunny,

Thanks again for your help. It worked perfectly !!

Xavier

• ###### 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)

• ###### 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))

• ###### Re: Find a unique max date

Can you explain how it works? stalwar1

• ###### 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?

• ###### Re: Find a unique max date

Is it good w.r.t performance?

• ###### Re: Find a unique max date

I have not done performance testing, but if you are comparing it to flag in script? I doubt that this will be better. But comparing it to another FirstSortedValue based on a single field, I don't think this would be so bad.

• ###### 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.

• ###### Re: Find a unique max date

Hi,

Maybe one last question, how can I create based on the same table a new dimmension with all my NAME's?

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 Mike 10/03/2016 2 30 Tom 10/03/2016 2 40 David 10/03/2016

Result is:

 ID NUM NAME DATE NEW DIMENSION 1 30 Tom 14/03/2016 John/Mike/Tom 2 40 David 10/03/2016 John/Mike/Tom/David

• ###### Re: Find a unique max date

This may be:

Table:

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, Mike, 10/03/2016

2, 30, Tom, 10/03/2016

2, 40, David, 10/03/2016

];

Left Join (Table)

Concat(NAME, '/') as [NEW DIMENSION]

Resident Table

Group By ID;

Right Join(Table)

Max(DATE) as DATE

Resident Table

Group By ID;

Right Join (Table)

Max(NUM) as NUM

Resident Table

Group By ID;

• ###### Re: Find a unique max date

Hi Sunny,

Once again it worked! thanks for you help

• ###### Re: Find a unique max date

One more version..

Data:

FirstSortedValue(NAME,-NUM) As NAME,

FirstSortedValue(DATE,-NUM) As DATE,

Concat(NAME, '/') as [NEW DIMENSION]

Group By ID

;

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, Mike, 10/03/2016

2, 30, Tom, 10/03/2016

2, 40, David, 10/03/2016

]

;