Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
tiklam_dynasys
Contributor

Get value of column C if Column B is max value in each item

Column AColumn BColumn C
A1311
A2015
B922
B325
C513
C810
C627

Result:

Column AColumn BColumn C
A2015
B922
C810

Many Thx!

1 Solution

Accepted Solutions

Re: Get value of column C if Column B is max value in each item

Try this

Dimension : coloumn A

Expr1: Firstsortedvalue (Coloumn B,-Coloumn B)

Expr2: Firstsortedvalue (Coloumn C,-Coloumn B)

8 Replies
apoorvasrivasta
New Contributor III

Re: Get value of column C if Column B is max value in each item

[Table 1]:

load * Inline

[ Column_A, Column_B , Column_C

  A, 13, 11

  A, 20, 15

  B, 9, 22

  B, 3, 25

  C, 5, 13

  C, 8, 10

  C, 6, 27];

[Table 2]:

LOAD Column_A,

max(Column_B) as Column_B

Resident  [Table 1]

group by Column_A;

drop table [Table 1];

left join

[Table 3]:

load * Inline

[ Column_A, Column_B , Column_C

  A, 13, 11

  A, 20, 15

  B, 9, 22

  B, 3, 25

  C, 5, 13

  C, 8, 10

  C, 6, 27]; !

oxslierlenin
New Contributor

Re: Get value of column C if Column B is max value in each item

Ejemplo_Aux:
LOAD * INLINE
[

Column_A,  Column_B,  Column_C
A,   13,  11
A,   20,  15
B,   9,   22
B,   3,   25
C,   5,   13
C,   8,   10
C,   6,   27

]

;

NoConcatenate
Ejemplo_Order:
LOAD

Column_A,
Column_B,
Column_C,
If(Previous(Column_A) = Column_A, Peek('Num') +1, 1) as Num

RESIDENT Ejemplo_Aux
ORDER BY Column_A, Column_B Desc;

NoConcatenate
Ejemplo_Fin:
LOAD
Column_A,
Column_B,
Column_C,
Num 
RESIDENT Ejemplo_Order
WHERE Num=1;

DROP TABLE Ejemplo_Aux;
DROP TABLE Ejemplo_Order

Espero que te sirva.

Re: Get value of column C if Column B is max value in each item

Hi Louis,

Data:
LOAD * INLINE [
Column A, Column B, Column C
A, 13, 11
A, 20, 15
B, 9, 22
B, 3, 25
C, 5, 13
C, 8, 10
C, 6, 27
]
;

Inner Join
Load 
[Column A]
Max([Column B]) as [Column B]
Resident Data 
Group By [Column A];


Result:

Capture.PNG

tiklam_dynasys
Contributor

Re: Get value of column C if Column B is max value in each item

Can i do it in expression?

miskin_m
Contributor

Re: Get value of column C if Column B is max value in each item

Hi,

Take max(Column B) as expression.

Re: Get value of column C if Column B is max value in each item

Try this

Dimension : coloumn A

Expr1: Firstsortedvalue (Coloumn B,-Coloumn B)

Expr2: Firstsortedvalue (Coloumn C,-Coloumn B)

tiklam_dynasys
Contributor

Re: Get value of column C if Column B is max value in each item

If there is two max value, how to handle?

Column AColumn BColumn C
A1311
A2015
B922
B325
C513
C810
C827

Result:

Column AColumn BColumn C
A2015
B922
C837

Best Regards,

Louis

Re: Get value of column C if Column B is max value in each item

May be like this:

Dimension

Column A

Expression

1) =Max([Column B])

2) =FirstSortedValue(Aggr(Sum([Column C]), [Column B]), -Aggr([Column B], [Column B]))

Capture.PNG