topic Re: Get 1st instance of max value in QlikView

Get 1st instance of max value

tchovanec — Wed, 21 Jan 2015 02:13:22 GMT

Table:

Load * inline

[

Pat_ID,Date,Score

1111,1/1/2014,1

1111,2/1/2014,1

1111,3/1/2014,3

1111,4/1/2014,3

2222,1/1/2014,1

2222,2/1/2014,3

2222,3/1/2014,3

3333,1/1/2014,2

3333,8/1/2014,4

3333,9/1/2014,4

];

What I would like to do is create a flag per Pat_ID that would flag the 1st instance of the max score. I would like the end table to look like this.

Pat_ID,Date,Score, Flg_Max

1111,1/1/2014,1,0

1111,2/1/2014,1,0

1111,3/1/2014,3,1

1111,4/1/2014,3,0

2222,1/1/2014,1,0

2222,2/1/2014,3,1

2222,3/1/2014,3,0

3333,1/1/2014,2,0

3333,8/1/2014,4,1

3333,9/1/2014,4,0

Any help would be greatly appreciated. Thank you

Re: Get 1st instance of max value

anbu1984 — Wed, 21 Jan 2015 06:57:06 GMT

Table:

Load * inline

[

Pat_ID,Date,Score

1111,1/1/2014,1

1111,2/1/2014,1

1111,3/1/2014,3

1111,4/1/2014,3

2222,1/1/2014,1

2222,2/1/2014,3

2222,3/1/2014,3

3333,1/1/2014,2

3333,8/1/2014,4

3333,9/1/2014,4

];

Join(Table)
Load Pat_ID,Max(Score) As Score, 1 As Flag Resident Table;

Final:
NoConcatenate
Load Pat_ID,Date,Score,If(IsNull(Flag),0,If(Pat_ID = Previous(Pat_ID) And Peek(Flag) = 0,1,0) As Flag Resident Table;

Drop table Table;

Re: Get 1st instance of max value

FernandaNava — Thu, 17 Sep 2020 15:04:55 GMT

Hello! I know this is an old post but I'm trying your solution for a similar application and I get an "Invalid expression" error for the Max() statement. I'm using qliksense april 2019.

I've made sure the field I'm comparing is a numeric one. Any clues why this may be happening?

Re: Get 1st instance of max value

edwin — Thu, 17 Sep 2020 15:41:49 GMT

the line with the Max(Score) is missing a group by expression group by Pat_ID
having surpassed that you will of course find the if statement is missing a closing parenthesis.