Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
abhinavbose
Creator III
Creator III

Pass multiple values using if statement

Hi,

I need to pass multiple values using if statement in QV front-end expression dynamically.

For example, 

=if(Customer = 'A' and Customer = 'B' and Customer = 'C' ,1,0)

I need the above expression dynamically using a variable. Is it possible? If it is, please help.

=if(Customer = $(vCustomer), 1,0)         --  I tried this but no use.

vCustomer = GetFieldSelections(Customer)

I also tried "Match" function, but I couldn't achieve "AND" operation in if statement since match function satisfies the condition if any one of the values is present. 

Attached my sample QVW with this.

Thanks,

5 Replies
Colin-Albert

Your expression and match() both work fine.
You need to change your total expression from "Total Count" to "Sum of Rows" to get the correct total. See attached.

Colin-Albert

Another expression that will work is

=count(if(match(Customer, 'A','B','C'), 1)) 

and set the Total mode to "Expression Default"

abhinavbose
Creator III
Creator III
Author

Thanks Colin. But I expect creating a variable and using it in the expression.

This match function works only for "OR" operation not for "AND" operation,

=if(Customer='A' and Customer='B' and Customer='C',1,0).

also this expression is static but I want it as a dynamic one, please suggest.

Colin-Albert

=if(Customer='A' and Customer='B' and Customer='C',1,0) will never work, as a customer cannot be A, B and C at the same time. 

I think you have your a ANDs and ORs mixed up,  or perhaps you have not explained your requirements.

Can you show some example data for Customer and the expected results?

abhinavbose
Creator III
Creator III
Author

Provided my requirements below.

1. Assume we have 4 customers (A, B, C & D) and each customer has its own fields and logics as mentioned below.

Customer              Field            Logic

A                                AB                 A11  

A                                AC                 B12

A                                AD                 D34

B                                AC                 J76

B                                AE                 E90

B                                AD                 F34

B                                AO                I87

C                                AB                 G23

C                                CD                 K45                  

D                                BO                A17

D                                AC                 C09

D                                CD                V89

 

Now, if a customer is selected (assume Customer A is selected). I need the below mentioned things.

1. Total Number of fields for the selected customer (3).

2. Reusable fields count matching exactly same with the other customers (excluding selected customer) ,

(2 fields of A exactly matches with fields of B, similarly 1 field matches with C and D).

3. Here is the critical part - Assume if we select the reusable fields count (2 matching fields from customer B), I want totally 3 fields in a table as shown below (since 3 is the total no. of fields of customer A).

Field       Logic

AB             A11           --  Not matched

AC             J76           -- Matched

AD             F34          -- Matched

 In the above table, Field AB from customer A is not used in customer B. So the logic should be A's logic (A11).

But the other fields AC and AD from customer A are used in customer B. So the logic should be B's logic (J76 and F34).

Note: The fields are same, only difference is Logic.

This is where my problem if we multi-select reusable fields of both B and C (from point 3) - I need to show both matching and unmatching fields. This is why I'm looking for a dynamic expression.