topic Re: Calculated dimension with multiple if's in QlikView

Calculated dimension with multiple if's

Fri, 29 Jan 2016 08:39:38 GMT

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

Re: Calculated dimension with multiple if's

sunny_talwar — Fri, 29 Jan 2016 12:39:43 GMT

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

Re: Calculated dimension with multiple if's

swuehl — Fri, 29 Jan 2016 12:44:57 GMT

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

Re: Calculated dimension with multiple if's

Anonymous — Fri, 29 Jan 2016 12:49:07 GMT

You reverts like a Bullet Train

Re: Calculated dimension with multiple if's

Fri, 29 Jan 2016 14:21:28 GMT

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.

Re: Calculated dimension with multiple if's

swuehl — Fri, 29 Jan 2016 17:10:33 GMT

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

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

Re: Calculated dimension with multiple if's

Mon, 01 Feb 2016 09:54:11 GMT

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

Re: Calculated dimension with multiple if's

swuehl — Mon, 01 Feb 2016 10:20:54 GMT

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;

Re: Calculated dimension with multiple if's

Peter_Cammaert — Mon, 01 Feb 2016 10:31:31 GMT

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...

Re: Calculated dimension with multiple if's

Mon, 01 Feb 2016 10:46:54 GMT

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

Re: Calculated dimension with multiple if's

Wed, 03 Feb 2016 15:26:23 GMT

Wow, this is too much rocket science for me..

If this is my original calculated dimension: If(type = 'B' and not Match(number ,'1','2'), 'test', number)

where do I put this? And you are talking about aggregated expressions, I have more than 10 expressions, what should I do with them?

Sorry for the possible stupid questions, your answer is just a bit too difficult for me.

Thanks in advance

Re: Calculated dimension with multiple if's

Peter_Cammaert — Thu, 04 Feb 2016 08:29:21 GMT

And it's becoming confusing for me.

You say you cannot use Suppress NULL values on the calculated dimension. Why not? The calculated dimension will produce a NULL value only under certain conditions that you do not want to show anyway. And the Suppress NULL values switch works only on the selected dimension column, not on the others that you may have.

It would be very helpful if you could upload a demo document that contains your case in its entirety. Omit everything else if not relevant.

BTW did you ever think about using set analysis? Performs much better on large data sets and it will do away with all this hacking. See attachment for different examples of excluding type='B' and number = '4' (your initial request), even when there are NULLs.

Peter

Re: Calculated dimension with multiple if's

Thu, 04 Feb 2016 10:12:32 GMT

Thank you for your answer. Underneath you can find my table (it's faster for me to explain it this way, I hope you don't mind).

The question: All numbers can stay, EXCEPT everything that is not equal to 1 ir 2 IF it is Type b. (The numbers in red).

My problem: if I use the formula If(type = 'Type b' and not Match(Number ,'1','2'), Null(), Number)

With this formula numbers 3 and 4 will become a Null() value, which is what I wanted. If I then select "suppress all Null() values", 3 and 4 will be gone, AND the two yellow boxes. It is ok for the upper yellow box to be gone, since it is not 1 or 2 and we are in type b. BUT there is a Null() value in Type c as well, and that one should stay.

I hope I made it more clear.. Do you have a solution for this?

Thanks in advance!

Edit: I looked at your QV-file but I don't really get your example... If I change you expression to "if((type <> 'B') or IsNull(number) or (Not Match(number, '4')), number) and then select "Suppress zero values", I lose all the zero values. Also then ones that should stay (in your example the one in type A, C and D.

Re: Calculated dimension with multiple if's

swuehl — Thu, 04 Feb 2016 17:45:11 GMT

What about something like

If(type = 'Type b' and not Match(Number ,'1','2'), Null(), If(Len(Trim(Number)),Number,'-'))

Re: Calculated dimension with multiple if's

Fri, 05 Feb 2016 08:10:55 GMT

This does not change anything. The null value in type C will still be there as null value, so I cannot "suppress zero values" or I will lose this information.

Re: Calculated dimension with multiple if's

Peter_Cammaert — Fri, 05 Feb 2016 10:22:36 GMT

Well, in the end the context becomes a lot clearer. You're using a pivot table, which does not support column hiding. My solutions were for a calculated dimension in a hidden column.

Use Stefan's suggestion together with "Suppress When Value is NULL" on the number column. That should work all right.

Peter

Re: Calculated dimension with multiple if's

Fri, 05 Feb 2016 10:52:51 GMT

I don't find a Stefan in my thread? Which user do you mean?

And I am very sorry, it is actually not a pivot table, that was just to make it more clear (visual). It is a normal chart.

I tried yours, and I understand the idea behind your solution. But the formule you gave me does not show me what I want. I am trying to tweak it a bit.

Edit: GOT IT! I tweaked you solution but it worked with the hidden "test" column!

I just need a small adaptation. It is more difficult than what we already found, but I feel we are close by

The second column is covered.I used this formula to get the correct answer:

=if((not Match (Type, 'Type b'')) or (Match(number, '1', '2')), 'test')

I now need something special for type d: type d can only be shown if the number plate is 111111 or 222222.

Basically, the orange boxes should stay and the red numbers should be gone. The issue with the formula written above: numbers 1 and 2 will stay, since I asked them to. Can I use some kind of combination in my calculated dimension? Something like: if it is type d AND number plate ('...'), then....?

Yet again, thanks in advance!

Re: Calculated dimension with multiple if's

swuehl — Fri, 05 Feb 2016 17:43:48 GMT

The calculated dimension should replace NULL in type 'c' with character '-'. So this should allow you to use the dimension option 'Suppress when value is NULL'.

Maybe I am missing something, could you upload a small sample QVW?

Re: Calculated dimension with multiple if's

Peter_Cammaert — Fri, 05 Feb 2016 18:20:15 GMT

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

Re: Calculated dimension with multiple if's

sunny_talwar — Fri, 05 Feb 2016 18:23:11 GMT

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

I second that