Lookup for Max Value in one column and based on this, calculate corresponding max value in another column

[qlikwiz123]

Here is my sample data.

ID	Ver1	Ver2
24	1	1
24	1	2
24	2	1
24	2	2
24	3	1
24	3	2
24	4	1
24	4	2
24	5	1
24	5	2
24	6	1
19	1	1
20	1	1
20	1	2
20	1	3
20	2	1
20	2	2
21	1	1
21	1	2
21	2	1
21	2	2
21	3	1
21	3	2
21	4	1

I need to create a button action where when clicked should show rows with Max Ver1 and corresponding ver2 values.

Output:

ID	Ver1	Ver2
24	6	1
19	1	1
20	2	2
21	4	1

Also, when I use ID as Dimension in Straight table and Ver1 and Ver2 as Expressions, I don't see values in Ver1 and Ver2 columns. But when I create a Table box, it shows all the data. How to fix this?

[rwunderlich]

A Table Box shows all values. It's basically a data dump.

A Straight Table is used to aggregate values.  If ID is the dimension, an expression like

=Ver1

will calculate a null. This is because you have not told Qlik how to handle (aggregate) the multiple values of Ver1 that occur for each ID value. Valid expressions would use an aggregation function like:

Sum(Ver1)

Max(Ver1)

-Rob

[MarcoWedel]

how is "Max Ver1 and ver2 values" defined in general?

[qlikwiz123]

I get that. I need a  Straight table as I have other calculations and Conditional Hide/Show expressions which cannot be done in a Table Box.

Doing a simple Max(Ver1) and Max(Ver2) won't get me the result as my use case is slightly different.

In the below example, max(ver1) will give 2 and Max(ver2) will give 3. But my Max(ver2) should be based on Max(ver1) and then check for the max value in Ver2.

20	1	1
20	1	2
20	1	3
20	2	1
20	2	2

Output

20

2

2

[qlikwiz123]

Just the highest number in Ver1 and Ver2 columns for any ID. So the button click should only show IDs and one row per ID with the highest value in Ver1 and Ver2.

Example, if ID 1 has 1,2 in Ver1 and 1,2,3 in Ver2, the button click should filter data and show ID, 2 in Ver1 and 3 in Ver2 columns since they are the highest numerical values in each column.

[MarcoWedel]

your sample output above does not reflect this, as the highest numbers of Ver2 are never shown?

[qlikwiz123]

My bad. That is right. Max of Ver1 and the respective highest value as per Max(Ver1).

24

6

1

In this case, since ID 24 has max Ver1 as 6 and the corresponding max Ver2 for Ver1=6 is 1, ver2 should show up as 1. Hope this clears the confusion.

In the below example, max(ver1) will give 2 and Max(ver2) will give 3. But my Max(ver2) should be based on Max(ver1) and then check for the max value in Ver2.

20	1	1
20	1	2
20	1	3
20	2	1
20	2	2

Output

20

2

2

[rwunderlich · Accepted solution]

Try:

Dimension: ID

Expression1:  Max(Ver1)

Expression2: FirstSortedValue(Ver2, -(Ver1 + (Ver2 * .001)))

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[qlikwiz123]

This did the job. Really appreciate your help @rwunderlich 

[qlikwiz123]

Hi @rwunderlich 

This is working when I feed data from Excel. But when I change the connection to a database (Hadoop) with the same data as excel, it is not working. Is there a known reason?