Conditional sum on multiple variables

[annegedal]

Hi,

I have the following data:

Event data:

case_id	task_id	Ressource1	Ressource2	Ressource3	hours
001	526	1	2	0	1,5
002	654	2	0	0	0,7
003	687	1	2	3	0,3

Ressource data:

Ressource_id	Ressource_name
1	joe
2	jane
3	peter

I would like to get an overview of the total hours each ressource has used according to my event-log data.

And return the following table:

Ressource_id	hours used
1	1,7
2	2,5
3	0,3

I have tried with many different ways, for example:

Sum({<Ressource1 *= {Ressource_id},Ressource2 *= {Ressource_id},Ressource2 *= {Ressource_id}>}hours)

But nothing seems to work. There must be a smart way!?  

-anne

[vamsee]

Maybe try doing this is the backend script.

Event_data:
LOAD * INLINE [

case_id, task_id, Ressource1, Ressource2, Ressource3, hours
001, 526, 1, 2, 0, 1.5
002, 654, 2, 0, 0, 0.7
003, 687, 1, 2, 3, 0.3
];

R1_Map:
Mapping Load
Ressource1,
SUM(hours) as Hours_used
Resident Event_data
Group By Ressource1;

R2_Map:
Mapping Load
Ressource2,
SUM(hours) as Hours_used
Resident Event_data
Group By Ressource2;

R3_Map:
Mapping Load
Ressource3,
SUM(hours) as Hours_used
Resident Event_data
Group By Ressource3;

DROP Table Event_data;

Ressource_data:
LOAD  *,
ApplyMap('R1_Map', Ressource_id,0)+ Applymap('R2_Map',Ressource_id, 0)+ Applymap('R3_Map',Ressource_id,0) as Hours_Used;
LOAD * Inline [
Ressource_id, Ressource_name
1, joe
2, jane
3, peter
];

If you don't want to modify your source tables then your can map Hours_used to your resource table again based on Resource_id

[annegedal]

Hi Vamsee,

thanks for the extensive feedback.
I was kinda hoping to avoid making an additional backendscript though 😕

I'm not sure i understand your last comment? wouldn't that still require the backend script?

-anne

[maxgro · Accepted solution]

try

sum(if(match(Ressource_id,Ressource1,Ressource2,Ressource3), hours))

[vamsee]

Yes.

If your dataset is not huge try the below as suggested by Massimo

[annegedal]

Thanks a lot both of you.
Massimos suggestion did the trick