Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Variable If Statement Help

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.

9 Replies
sushil353
Honored Contributor II

Re: Variable If Statement Help

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

Not applicable

Re: Variable If Statement Help

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.

Not applicable

Re: Variable If Statement Help

Just tried that with no success.

qlikdeez
Valued Contributor

Re: Variable If Statement Help

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.

  

OccupationChangeTypeChangePerc
AML / KYC Subject Matter ExpertNo change1
AML / KYC Subject Matter ExpertSmall0,1
AML / KYC Subject Matter ExpertMedium0,2
AML / KYC Subject Matter Expertlarge0,3
MI LeadNo change1
MI LeadSmall0,1
MI LeadMedium0,2
MI Leadlarge

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!

Not applicable

Re: Variable If Statement Help

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

qlikdeez
Valued Contributor

Re: Variable If Statement Help

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.

qlikdeez
Valued Contributor

Re: Variable If Statement Help

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.

Not applicable

Re: Variable If Statement Help

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?

qlikdeez
Valued Contributor

Re: Variable If Statement Help

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