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

[Anonymous]

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

Result:

Column A	Column B	Column C
A	20	15
B	9	22
C	8	10

Many Thx!

[Anonymous]

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

[Anonymous]

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.

[tamilarasu]

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:

[Anonymous]

Can i do it in expression?

[miskin_m]

Hi,

Take max(Column B) as expression.

[settu_periasamy · Accepted solution]

Try this

Dimension : coloumn A

Expr1: Firstsortedvalue (Coloumn B,-Coloumn B)

Expr2: Firstsortedvalue (Coloumn C,-Coloumn B)

[Anonymous]

If there is two max value, how to handle?

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	8	27

Result:

Column A	Column B	Column C
A	20	15
B	9	22
C	8	37

Best Regards,

Louis

[sunny_talwar]

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