How to divide a row with another and display

[Uday]

Hi All,

I have a table with different metrics. In a chart I would like to get data by dividing one row by another based on the data from a single column and show the aggregate as a row.

Actual table:
Col A Col B Col C
201801 b1 100
201801 b2 60
201801 max 30
201802 b1 160
201802 b2 120
201802 max 40

Result should be
Col A Col B Col c
201801 b1/max 3
201801 b1 90
201801 b2/max 2
201801 b2 60
201801 max 30
201802 b1/max 4
201802 b1 160
201802 b2/max 3
201802 b2 120
201802 max 40

I want to divide b1/max in the column B and get aggregate sum from column c as a new row with Column B value as maxB1.

I need to do it as an expression.

Thank you

[uacg0009]

Hi,
I have a question, why is there no "201802 b2 120" in "Result should be"?
Aiolos

[Uday]

It should be I missed mentioning it

[uacg0009]

I think below row is also wrong in your "Result should be":

201801 b1 90

it should be :

201801 b1 100

And there is an easy way to solve that, or you can use loop.

Easy way:

t1:
LOAD * INLINE [
    Col A, Col B, Col C
    201801, b1, 100
    201801, b2, 60
    201801, max, 30
    201802, b1, 160
    201802, b2, 120
    201802, max, 40
];

Left Join
LOAD [Col A],
	 [Col C] as [max Col C]
Resident t1
Where [Col B] = 'max';

Concatenate
LOAD [Col A],
	 [Col B] & '/max' as [Col B],
	 Div([Col C],[max Col C]) as [Col C]
Resident t1
Where [Col B] <> 'max';

DROP Field [max Col C];

Please try, it works in my desktop.

Aiolos Zhao