Find a unique max date

[reivax31]

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.

Thanks for your help

[sunny_talwar]

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.

[reivax31]

Hi Sunny,

Thanks again for your help. It worked perfectly !!

Xavier

[reivax31]

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

Thanks for your help

[sunny_talwar]

This may be:

Table:

LOAD * INLINE [

    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)

LOAD ID,

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

Resident Table

Group By ID;

Right Join(Table)

LOAD ID,

  Max(DATE) as DATE

Resident Table

Group By ID;

Right Join (Table)

LOAD ID,

  Max(NUM) as NUM

Resident Table

Group By ID;

[Saravanan_Desingh]

One more version..

Data:

Load ID, Max(NUM) As NUM,

  FirstSortedValue(NAME,-NUM) As NAME,

  FirstSortedValue(DATE,-NUM) As DATE,

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

Group By ID

;

LOAD * INLINE [

    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

]

;

[reivax31]

Hi Sunny,

Once again it worked! thanks for you help