Set analysis: how to use multiple IFs/Matches in load statement

paul_ripley — Wed, 29 Aug 2018 16:20:54 GMT

Hello

I have a set of data, and the data in the source system is split into teams eg Finance, P&P, IT etc.

Then there is breakdown of the costs into type of expense called 'compen'. But Finance, IT and P&P use a different mapping for 'compen' than every other department, so I created a calculated dimension as below to cater for this, and it works fine

=if(wildmatch(team_description, 'Finance, Tax and Treasury','IT','P&P') >0, bud2_compen2, bud2_compen)

However, I now need to split out IT into 'IT' and 'IT Non-People Costs'. The distinction between the 2 is 'IT Non-people costs' is where bud2_compen2 = 'office costs'

So the logic that I think I need to write in the calculated dimension is something like below

=if(wildmatch(team_description, 'Finance, Tax and Treasury','IT','P&P') >0, bud2_compen2, bud2_compen)

and if(wildmatch(team_description, 'IT') >0 and bud2_compen2 = 'office costs', 'IT Non-People Costs' , team_description)

I also thought maybe i could do the renaming of the team in the script but that doesnt work also as bud2_compen2 doesnt exist ar this point as it is a mapping that is included in the dataload.

Not sure if any of that makes sense, but can only suggest anything to help. Thanks Paul

busunit:
LOAD team,
team_description,

if(wildmatch(team_description, 'IT') >0 and bud2_compen2 = 'office costs', 'IT Non-People Costs' , team_description) as team_descr,

PE as PE_Alloc%,
INF as Infra_Alloc%,
PC as PC_Alloc%
FROM
[\\3i.3iGroup.com\DFS\Shared\GRP_REPORT\QlikView\Allocations.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Set analysis: how to use multiple IFs/Matches in load statement

adityaakshaya — Wed, 29 Aug 2018 17:41:53 GMT

Hi Paul,

If your team_description only includes IT as a value, then you don't need to use wildmatch and the logic you have written will perform perfectly.

if( team_description = 'IT' and bud2_compen2 = 'office costs','IT Non - People Costs',team_description) as Team_descr

Hope this helps.

Regards,

Akshaya

Re: Set analysis: how to use multiple IFs/Matches in load statement

paul_ripley — Thu, 30 Aug 2018 08:30:22 GMT

Hi Akshaya

Thanks for replying. The problem is that when I run this bud2_compen2 isn't yet loaded into QV, it gets loaded later on.

I did speak to the user and he has said I don't need to do this at this point so I will find another way to work around it.

Thanks for your help

Paul

topic Re: Set analysis: how to use multiple IFs/Matches in load statement in QlikView

Set analysis: how to use multiple IFs/Matches in load statement

Re: Set analysis: how to use multiple IFs/Matches in load statement

Re: Set analysis: how to use multiple IFs/Matches in load statement