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