Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
Hi
The function MAX is a good soluction to use into the chart.
LOAD
ID,
YEAR,
PRODUCTID,
if(IDversion=3,IDversion,'optional')as IDVersion from path;
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
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.
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
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, ....)
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
Right Join
LOAD ID, ProductID,
Max(IDVersion) as IDVersion
Resident tabInput
Group By ID, ProductID;