8 Replies Latest reply: Jan 23, 2017 5:53 AM by Sunny Talwar

# 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!

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

[Table 1]:

[ 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]:

max(Column_B) as Column_B

Resident  [Table 1]

group by Column_A;

drop table [Table 1];

left join

[Table 3]:

[ 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]; !

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

Ejemplo_Aux:
[

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:

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:
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
[Column A]
Max([Column B]) as [Column B]
Resident Data
Group By [Column A];

Result:

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

Can i do it in expression?

• ###### 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)

• ###### 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]))