Solved: Re: Calculated dimension with multiple if's - Qlik Community

Report Inappropriate Content · ‎2016-01-29

Hi all,

I have a question concerning calculated dimensions.

I have 2 dimensions:

1) type= A, B, C, D

2) number= 1, 2, 3, 4

I want to show types A, B and C, BUT for B I only want to show 1, 2 and 3.

So A and C can have all the numbers, but B can only show numbers 1, 2 and 3.

Thanks in advance!

Kind regards

Peter_Cammaert · ‎2016-02-05

Stefan = swuehl. Remember that name, because in a while he will be one of your QlikView Gods too

If you have additional conditions, you can add them to your IF using simple Boolean logic (or, and, not etc.) Take care to use the proper number of parentheses. Remember that too many parentheses won't harm but too few almost certainly will.

If I get it right, you may want to try something like:

=IF ((Match(Type, 'Type b'') and Match(Number, '3', '4'))

or (Match(Type, 'Type d') and Not Match([Number plate], '111111', '222222')), NULL(), 'test')

I did use the field names as specified in your screenshot.

You can also inverse this logic to do away with the NULL result.

Peter

View solution in original post

sunny_talwar · ‎2016-01-29

May be these:

Dimension 1: type If(type <> 'D', type)

Dimension 2: If(type = B and number = 4, Null(), number)

Select 'Suppress When Value Is Null' for your second both dimension

Updated my comment after Stefan's comment and looking at the requirement again

swuehl · ‎2016-01-29

Dimension 1: if(type <> 'D', type)

Dimension 2: If(type = 'B' and number = 4, Null(), number)

Select 'Suppress When Value Is Null' for both dimensions

Anonymous · ‎2016-01-29

You reverts like a Bullet Train

Report Inappropriate Content · ‎2016-01-29

Damn, my example was too easy.

I should be using multiple values in the second dimension. Let's say B can only show numbers 1 and 2, not 3 and 4.

I tried

If(type = 'B' and number <> '1' or '2', Null(), number)

But that does not work, shince it shows me the Null() value for all of the types now. I tested it with

If(type = 'B' and number <> '1' or '2', 'test', number) and it shows me "test" on every line now.

swuehl · ‎2016-01-29

If(type = 'B' and number >2, Null(), number)

or

If(type = 'B' and not Match(number ,'1','2'), Null(), number)

Report Inappropriate Content · ‎2016-02-01

Thank you, that did work indeed.

I now have another problem thought. I cannot use this formule since I have Null() values in other types too, and those are OK to stay. So I cannot use the "suppress when value is Null" or I will lose other informatie.

Is there any possible way to tell Qlikview to suppress values when they have a specific value? E.g. if I use this formula:

If(type = 'B' and not Match(number ,'1','2'), 'be gone', number). Can I ask QV to suppress all values with the value "be gone"?

Maybe with the "Enable Conditional" field? I don't know what it is used for.

Thanks in advance

swuehl · ‎2016-02-01

The 'enable conditional' will work as a global switch for the dimension, not per dimension line.

I think it would be easier if you replace the NULL in your type field with some value, e.g. 'UNKNOWN' in the LOAD script:

LOAD if(len(trim(type)),type, 'UNKNOWN') as type,

...

FROM YourTable;

Peter_Cammaert · ‎2016-02-01

Yes, there is a (dirty) trick to do that. It consists of embedding all your expressions in a simple test like the one you used in your calculated condition. All expressions returning NULL will produce supressible rows. Like

=IF((type <> 'B') or (Not Match(number, '3', '4')), YourAggregationExpression)

You cannot use set analysis to do that with the results of a calculated dimension because set analysis comes first. You may succeed in creating a set analysis spec that does the same. It will look better, and will be better performing.

If you use the IF trick or decent set analysis, you can drop the calculated dimension altogether. It looks and feels like a kludge...

Report Inappropriate Content · ‎2016-02-01

Is there no other way than having to touch the script?