Trouble with Max Function

[Anonymous]

Hello dear community, I'm having a hard time with the max function. I'll try to explain my problem the best way I can.

I have the following data set:

What I want to do, is get all the product ID's that have the highest ID version.

So according to the data set, with the correct script, I should get:

Hope you can help me.

Best Regards.

[maxgro]

If I understand,  think the result is five product for ID 1863

1863 2000 18856 4 10

1863 2000 12355 1 1

1863 2000 78456 2 3

1863 2000 11111 3 2

1863 2000 12345 2 10

1863 2000 18856 1 4

1863 2000 11111 3 10

1863 2000 11111 1 8

anyway, this is my try

source:

LOAD ID,

    Year,

    ProductID,

    Sales,

    IDVersion

FROM

[https://community.qlik.com/thread/166605?sr=inbox&ru=61824]

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

final:

NoConcatenate load

  *

  ,if(ID<>peek(ID) or ProductID<>peek(ProductID),1) as flag          // comment, for check

Resident source

//where ID<>peek(ID) or ProductID<>peek(ProductID)                  // uncomment  for final result

order by

  ID, ProductID, IDVersion desc;

DROP Table source;

!

[Anonymous]

There is a slight problem with your solution, the data that I need are just the ones that has the highest value on IDVersion

[mdmukramali]

Dear FranciscoAcunac,

maxgro Solution working  fine.

can you check the attached solution.

Thanks,

Mukram.

[Anonymous]

There are certain data that should not appear. As it's shown in the following picture, for the ID 1863, the selections should only be for the IDVersion=10, but there are two selections that has an IDVersion lower than 10.

[mdmukramali]

Dear ,

For ID=1863 and ProductID=12355 the  you have only One Record that IDVersion =1

so it's showing that record.

Thanks,

Mukram

[Anonymous]

It doesn't matter that I lose a record, I just need the Product ID with the highest IDversion.
So step by step I would have to do:
1. I select all the data with the same ID

2. With previous selection, I select all the rows that contains the highest IDVersion

This process must be done for all the sets with the same ID

[MarcoWedel]

so actually the result should not be grouped by ID and ProductID like you stated before.

maybe

Right Join
LOAD ID,
    Max(IDVersion) as IDVersion
Resident tabInput
Group By ID;

could be a solution then.

regards

Marco

[MarcoWedel · Accepted solution]

one example:

tabInput:
LOAD * FROM [https://community.qlik.com/thread/166605] (html, codepage is 1252, embedded labels, table is @2);

Right Join
LOAD ID,
     Max(IDVersion) as IDVersion
Resident tabInput
Group By ID;

hope this helps

regards

Marco

[Anonymous]

Thanks a lot!
This is working so far!!

Thanks to the both of you!

[MarcoWedel]

nice that it works.

please close your thread if your question is answered.

thanks

regards

Marco