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]

RESULT

EDIT: sorry,  I used 1352 instead of 1353, the script is the same, just change the number

SCRIPT

source:

load * inline [

ID, Year, ProductID, Sales, IDVersion

1352,2012,10115,3,2

1352,2012,10116,4,2

1352,2012,13252,1,1

1352,2012,10116,1,1

1352,2012,10115,3,3

1352,2012,10116,4,3

1352,2012,13252,4,3

];

final:

NoConcatenate load

  *

Resident source

where ProductID <> Peek(ProductID)

order by ProductID, IDVersion desc;

drop Table source;

[ronald_gdi]

Hi

The function MAX is a good soluction to use into the chart.

[pratap6699]

LOAD

ID,

YEAR,

PRODUCTID,

if(IDversion=3,IDversion,'optional')as IDVersion from path;

[MarcoWedel]

Hi,

another solution could be:

tabInput:
LOAD * Inline [
ID, Year, ProductID, Sales, IDVersion
1353,2012,10115,3,2
1353,2012,10116,4,2
1353,2012,13252,1,1
1353,2012,10116,1,1
1353,2012,10115,3,3
1353,2012,10116,4,3
1353,2012,13252,4,3
];

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

hope this helps

regards

Marco

[Anonymous]

I'm sorry, I didn't explain myself as well as I should have.

This is another set of data that explains the situation a different but more accurate way:

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:

The solution could work on a script level or a front-end level.

[mdmukramali]

Dear Francisco,

Instead of attaching the image can you attach the data so that we can load the data into Qlikview to help you.

Like :

ID	Year	ProductID	Sales	IDVersion
1353	2012	10115	3	2
1353	2012	10116	4	3
1353	2012	13252	1	1

etc.

Thanks,

Mukram

[maxgro]

why do you have the same product (10115, ...) in 2 rows?

do you want the max ID version by

.- product

- or product and year

- or product and id

- or ID version (highest is 10)

- or ????

and please, add some data (excel, txt, qvd, ....)

[Anonymous]

  So sorry, here it's the data:

ID	Year	ProductID	Sales	IDVersion
1353	2012	10115	3	2
1353	2012	10116	4	2
1353	2012	13252	1	1
1353	2012	10116	1	1
1353	2012	10115	3	3
1353	2012	10116	4	3
1353	2012	13252	4	3
1363	2012	10115	3	4
1363	2012	10122	2	2
1363	2012	13252	1	4
1363	2012	10122	1	4
1363	2012	13252	5	3
1363	2012	10115	2	1
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

Result:

ID	Year	ProductID	Sales	IDVersion
1353	2012	10115	3	3
1353	2012	10116	4	3
1353	2012	13252	4	3
1363	2012	10115	3	4
1363	2012	13252	1	4
1363	2012	10122	1	4
1863	2000	18856	4	10
1863	2000	12345	2	10
1863	2000	11111	3	10

About the question:

The result should be grouped by ID and ProductID

Thanks Alot

[MarcoWedel]

Right Join 

LOAD ID, ProductID, 

    Max(IDVersion) as IDVersion 

Resident tabInput 

Group By ID, ProductID;