topic Re: Trouble with Max Function in QlikView

Trouble with Max Function

Anonymous — Tue, 02 Jun 2015 18:33:24 GMT

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.

Re: Trouble with Max Function

maxgro — Tue, 02 Jun 2015 18:55:47 GMT

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;

Re: Trouble with Max Function

ronald_gdi — Tue, 02 Jun 2015 18:57:39 GMT

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

Re: Trouble with Max Function

pratap6699 — Tue, 02 Jun 2015 19:24:15 GMT

LOAD

ID,

YEAR,

PRODUCTID,

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

Re: Trouble with Max Function

MarcoWedel — Tue, 02 Jun 2015 22:04:54 GMT

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

Re: Trouble with Max Function

Anonymous — Wed, 03 Jun 2015 12:53:56 GMT

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.

Re: Trouble with Max Function

mdmukramali — Wed, 03 Jun 2015 13:24:03 GMT

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

Re: Trouble with Max Function

maxgro — Wed, 03 Jun 2015 13:47:10 GMT

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, ....)

Re: Trouble with Max Function

Anonymous — Wed, 03 Jun 2015 14:21:38 GMT

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

Re: Trouble with Max Function

MarcoWedel — Wed, 03 Jun 2015 14:26:56 GMT

Right Join

LOAD ID, ProductID,

Max(IDVersion) as IDVersion

Resident tabInput

Group By ID, ProductID;

Re: Trouble with Max Function

maxgro — Wed, 03 Jun 2015 14:47:41 GMT

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;

Re: Trouble with Max Function

Anonymous — Wed, 03 Jun 2015 15:13:03 GMT

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

Re: Trouble with Max Function

mdmukramali — Wed, 03 Jun 2015 15:27:25 GMT

Dear FranciscoAcunac,

maxgro Solution working fine.

can you check the attached solution.

Thanks,

Mukram.

Re: Trouble with Max Function

Anonymous — Wed, 03 Jun 2015 15:44:49 GMT

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.

Re: Trouble with Max Function

mdmukramali — Wed, 03 Jun 2015 16:04:31 GMT

Dear ,

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

so it's showing that record.

Thanks,

Mukram

Re: Trouble with Max Function

Anonymous — Wed, 03 Jun 2015 17:24:33 GMT

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

Re: Trouble with Max Function

MarcoWedel — Wed, 03 Jun 2015 17:34:50 GMT

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

Re: Trouble with Max Function

MarcoWedel — Wed, 03 Jun 2015 17:39:51 GMT

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

Re: Trouble with Max Function

Anonymous — Wed, 03 Jun 2015 18:12:54 GMT

Thanks a lot!
This is working so far!!

Thanks to the both of you!

Re: Trouble with Max Function

MarcoWedel — Wed, 03 Jun 2015 18:14:04 GMT

nice that it works.

please close your thread if your question is answered.

thanks

regards

Marco