Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

annegedal
New Contributor

Conditional sum on multiple variables

Hi,

I have the following data:

Event data:

case_idtask_idRessource1Ressource2Ressource3hours
0015261201,5
0026542000,7
0036871230,3

Ressource data:

Ressource_idRessource_name
1joe
2jane
3peter

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_idhours used
11,7
22,5
30,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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Conditional sum on multiple variables

try

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

5 Replies
vamsee99999
Valued Contributor

Re: Conditional sum on multiple variables

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
New Contributor

Re: Conditional sum on multiple variables

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

MVP
MVP

Re: Conditional sum on multiple variables

try

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

vamsee99999
Valued Contributor

Re: Conditional sum on multiple variables

Yes.

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

annegedal
New Contributor

Re: Conditional sum on multiple variables

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