Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jadewind
Contributor

Filtering a dimension that has IF statement from two different fields

Hi everyone,

I need to make a category dimension using two different fields and I can successfully do it using an expression like below:

If(Field1 = 'A', 'C', if(Field2 = 'B', 'C', 'D'))

//if either Field1 is A or Field2 is B, category = C, otherwise D.

Now I want to filter the data to show only 'C' category. However the filtered data still shows some records that belong to 'D' category. It seems that when Field1 = 'A' and Field2 <> 'B', or Field1 <> 'A' but Field2 = 'B', these values from the two fields still show, although neither of them meet the criteria. I think it is because QS would still look at Field1 and Field2 although I ask it to look at the newly created Category dimension? If I'm not clear you can see example below:

Where Field1 = A, Field2 can be B, E, F

Where Field2 = B, Field1 can be A, G, H

So the filtered result will have all combinations of Field1 = A, G, H and Field2 = B, E, F, including those that are neither A or B.

So how do I ask QS to show only C category records??

Thanks in advance for any help!

1 Solution

Accepted Solutions
MVP
MVP

Re: Filtering a dimension that has IF statement from two different fields

When you make selections on a calculated dimension, you need to remember that you are making a selection on the underlying fields - in your case the combination of Field1 and Field2. You are not selecting "C" itself.

This may or may not allow "D" as the calculated value as well as "C", depending on your data model.

8 Replies
jadewind
Contributor

Re: Filtering a dimension that has IF statement from two different fields

BTW I do not have access to the LOAD script or the Master Items so I can only create this dimension every time I need it.

Lech_Miszkiewicz
Honored Contributor III

Re: Filtering a dimension that has IF statement from two different fields

Hi,

How about something like:

If(Field1 = 'A' or Field2 = 'B', 'C', 'D')


regards

Lech

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
jadewind
Contributor

Re: Filtering a dimension that has IF statement from two different fields

Thanks Lech. I already tried this and it worked the same way....still doesn't solve the problem.

Lech_Miszkiewicz
Honored Contributor III

Re: Filtering a dimension that has IF statement from two different fields

Hi

Would you be able to provide a qvf or qvw file?

Regards

Lech

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
shraddha_g
Honored Contributor III

Re: Filtering a dimension that has IF statement from two different fields

Try

if(wildmatch(Field1,'A') or wildmatch(Field2,'B') ,'C','D')

MVP
MVP

Re: Filtering a dimension that has IF statement from two different fields

When you make selections on a calculated dimension, you need to remember that you are making a selection on the underlying fields - in your case the combination of Field1 and Field2. You are not selecting "C" itself.

This may or may not allow "D" as the calculated value as well as "C", depending on your data model.

jadewind
Contributor

Re: Filtering a dimension that has IF statement from two different fields

Thanks Jonathan. That's what I thought. But how can I get around to get only records with C? I cannot change the data model because it's designed by someone else and I am only a user not having access to it.

jadewind
Contributor

Re: Filtering a dimension that has IF statement from two different fields

Hi Jonathan, I have tested a sample in my QS Desktop and it works fine. So I think you are right, there must be something wrong with the data model...

Community Browser