Filtering a dimension that has IF statement from two different fields

[Anonymous]

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!

[Anonymous]

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]

Hi,

How about something like:

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

regards

Lech

[Anonymous]

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

[Lech_Miszkiewicz]

Hi

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

Regards

Lech

[shraddha_g]

Try

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

[jonathandienst · Accepted solution]

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.

[Anonymous]

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.

[Anonymous]

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