Hello Team,
I have 2 look up data and I want to join them through a common field MonthYear. I need to calculate transmission per dept = Total transmission *(size of dept/total size of dept)
In lookup1 I need to calculate the propotion of size based on dept eg;
Transmission for Eng dept = 119 *((100+23)/ 170)
Lookup1:
MonthYear |
size |
org |
dept |
July 2022 |
100 |
research |
Eng |
July 2022 |
23 |
research |
Eng |
July 2022 |
2 |
data |
IU |
July 2022 |
45 |
research |
Lab |
Total size |
170 |
Lookup2:
MonthYear |
Transmission |
ID |
July 2022 |
60 |
global |
July 2022 |
34 |
global |
July 2022 |
23 |
Pbg |
July 2022 |
2 |
pcf |
Total transmission |
119 |
I made a merge of 2 lookup with join using MonthYear but I am able to pass only one token value at a time. I need to get pie chart based on calculated formula for org and sort for top values in dept
Code:
|inputlookup lookup2.csv
|search MonthYear="July 2022"
|join MonthYear
[|inputlookup lookup1.csv]
|stats sum(Transmission) as TotalTransmission, sum(size) as Totalsize by MonthYear
|join MonthYear
[|inputlookup lookup1.csv
|search dept="Eng"
|stats values(MonthYear) as MonthYear,sum(size) as DeptMem by dept]
|eval "Transmission per dept" = round(("Transmission per dept") * (DeptMem/Totalsize),2)
|fields "Transmission per dept"
Thank you this worked
I have 2 look up data and I want to join them through a common field MonthYear. I need to calculate transmission per dept = Total transmission *(size of dept/total size of dept)
In lookup1 I need to calculate the propotion of size based on dept eg;
Transmission for Eng dept = 119 *((100+23)/ 170)
| inputlookup lookup1.csv
| stats sum(size) as DeptMem by dept
| eventstats sum(DeptMem) as TotalSize
| append
[inputlookup lookup2.csv
| stats sum(Transmission) as TotalTransmission]
| eventstats values(TotalTransmission) as TotalTransmission
| eval "transmission per dept" = round(TotalTransmission * DeptMem / Totalsize, 2)
| stats values('transmission per dept') as "transmission per dept" by dept
Note: