Hi,
I want to total of some values with respect to dimension. So in front end I am using TOTAL function, but I want to total the values with respect to dimension at script level. As we cannot use TOTAL function in Script. So I am using group by function.
The issue with group by function is that: I have about to 70 fields and when I am using group by function with all of the fields then server going in hang situation.
When I am using group by function wrt 3-4 dimentions/fields then I cannot explore my data wrt other dimensions.
So kindly suggest any other method.
Below is my output from front end:
COEP_Cost_Center | Allocation | Sum(Cost_INR) | sum(distinct ProdQty) | Sum(Cost_INR)*sum(distinct ProdQty)/sum(total<COEP_Cost_Center>ProdQty) |
---|---|---|---|---|
40026197.43 | 34029.474 | |||
1001100109 | 1001100101 | 13342065.81 | 2725.306 | 1068521.1297826 |
1001100109 | 1001100102 | 13342065.81 | 30279.697 | 11871876.423387 |
1001100109 | 1001100103 | 13342065.81 | 1024.471 | 401668.25683043 |
Below is the script and output:
COEP3:
LOAD
COEP_Cost_Center,
Allocation,
Sum(Cost_INR)*sum(distinct ProdQty) as Qty1,
sum(ProdQty) as Qty2
Resident COEP2
Group by COEP_Cost_Center,
Allocation;
DROP Table COEP2;
COEP_Cost_Center | Allocation | Sum(Qty1)/Sum(Qty2) | Sum(Qty1) | Sum(Qty2) |
---|---|---|---|---|
16189.30 | 454023481587.68 | 28044665.77 | ||
1001100109 | 1001100101 | 15896.43 | 36361212004.39 | 2287382.54 |
1001100109 | 1001100102 | 15727.80 | 403993710080.86 | 25686594.73 |
1001100109 | 1001100103 | 193363.27 | 13668559502.44 | 70688.50 |
The Red colored values are by group by function.
Seems like this will have to be a two step process
COEP3:
LOAD
COEP_Cost_Center,
Allocation,
Sum(Cost_INR)*sum(distinct ProdQty) as Qty1
Resident COEP2
Group by COEP_Cost_Center, Allocation;
Left Join (COEP3)
LOAD COEP_Cost_Center,
sum(ProdQty) as Qty2
Resident COEP2
Group By COEP_Cost_Center;
DROP Table COEP2;