topic Re: Variable If Statement Help in QlikView

Variable If Statement Help

Wed, 16 Nov 2016 11:08:01 GMT

Hi All,

I have a slider called [Change Regulation] which has 4 selections:

No Change

Small

Medium

Large

Outside of this I have 8 different Occupation types:

AML / KYC Subject Matter Expert

MI Lead

Operations Head

Population Lead

Quality Assurance

Relationship Owner

Review Analyst

Team Lead

These will change based on which selection is made in Change Regulation. So I have set up a variable called vRegulation and attempted to do an if statement for these. This should be how it should look:

No Change = No change for any of the Occupations

Small = Review Analyst & Relationship Owner increase by 2%, Operations Head is no change and rest are increased by 0.5%

Medium = Review Analyst & Relationship Owner increase by 5%, Operations Head is no change and rest are increased by 1%

Larger = Review Analyst & Relationship Owner increase by 15%, Operations Head is no change and rest are increased by 2%.

If anyone could help me with how this If statement would look in the variable it would be much appreciated as I have had no joy so far.

Re: Variable If Statement Help

sushil353 — Wed, 16 Nov 2016 11:14:10 GMT

it would be easy if you create 8 different variable for each Occupation Type:

vSubjectMatterExpert

vMILead

vOperationsHead

vPopulationLead

vQualityAssurance

vRelationshipOwner

vReviewAnalyst

vTeamLead

and use if condition to assign respective value to each variable

Re: Variable If Statement Help

Wed, 16 Nov 2016 11:32:36 GMT

Hi Sushil,

Thanks for the response, how would you set this IF statement up in say vReviewAnalyst?

Would it be

=if(Occupation='Review Analyst',if([Change Regulation]='Small',0.02,if([Change Regulation]='Medium',0.05,if([Change Regulation]='Large',0.15,0))))

Would that work? Sorry not overly great with these.

Re: Variable If Statement Help

Wed, 16 Nov 2016 11:38:34 GMT

Just tried that with no success.

Re: Variable If Statement Help

Anonymous — Wed, 16 Nov 2016 11:57:07 GMT

Hi,

Might be easier to keep a matrix in excel and map this to your occupation data

i.e subset sample of how the data will look.

Occupation	ChangeType	ChangePerc
AML / KYC Subject Matter Expert	No change	1
AML / KYC Subject Matter Expert	Small	0,1
AML / KYC Subject Matter Expert	Medium	0,2
AML / KYC Subject Matter Expert	large	0,3
MI Lead	No change	1
MI Lead	Small	0,1
MI Lead	Medium	0,2
MI Lead	large	0,3

The your slider acts like a selector for the ChangeType Column above.

Then in your expression sum(YourValue) * ChangePerc

Best to have that left joined to your fact table which should have the main measure you use in your expression for performance reasons.

Hope this helps!

Re: Variable If Statement Help

Wed, 16 Nov 2016 12:11:08 GMT

Hi,

Thanks for the reply, I am not great with mapping (not great with much it seems). I would be more comfortable with if statements in variables although I cannot even get those to work!

Regards

David

Re: Variable If Statement Help

Anonymous — Thu, 17 Nov 2016 06:38:21 GMT

The approach above should be easier, cleaner and better performance.

If you create the matrix as advised above, load the excel file by map i mean you could simply load it in as normal with Occupation field from the above file associated/linked with the occupation field in you fact table.

You can then just use the the expression above.

Re: Variable If Statement Help

Anonymous — Thu, 17 Nov 2016 06:47:12 GMT

If you absolutely must use if statements, use this tool:

qlikblog.at - Nested If Generator

Its a massive help for complex if statements.

Just make sure you test each condition seperately to see if they produce correct results and the tool above will help structure it in a easy to understand way.

Re: Variable If Statement Help

Thu, 17 Nov 2016 09:46:27 GMT

Hi,

Going to try and give this a go, I have loaded the file in with the 3 fields above. My expression looks like this currently:

+ sum({<Year={'2018'}, Attrition=, Retirement=>}[Demand C])*($(vNewClients) - $(vAutomation) + $(vRisk) + vRegulation )

With vRegulation being the current Change Regulation Piece. Would I just add (vRegulation * ChangePerc) to that and it should work?

Re: Variable If Statement Help

Anonymous — Thu, 17 Nov 2016 10:04:38 GMT

Logically, Yes it should work if its setup as explained...

Use your usual expression if it produces the desired result and * by ChangePerc

(YourExp) * Change Perc