Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try this
Dimension : coloumn A
Expr1: Firstsortedvalue (Coloumn B,-Coloumn B)
Expr2: Firstsortedvalue (Coloumn C,-Coloumn B)
[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]; !
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.
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:
Can i do it in expression?
Hi,
Take max(Column B) as expression.
Try this
Dimension : coloumn A
Expr1: Firstsortedvalue (Coloumn B,-Coloumn B)
Expr2: Firstsortedvalue (Coloumn C,-Coloumn B)
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
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]))