topic Pass multiple values using if statement in QlikView

Pass multiple values using if statement

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Pass multiple values using if statement

Colin-Albert — Tue, 04 Dec 2018 14:58:01 GMT

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.

Re: Pass multiple values using if statement

Colin-Albert — Tue, 04 Dec 2018 15:19:12 GMT

Another expression that will work is

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

and set the Total mode to "Expression Default"

Re: Pass multiple values using if statement

Anonymous — Tue, 04 Dec 2018 15:54:03 GMT

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.

Re: Pass multiple values using if statement

Colin-Albert — Tue, 04 Dec 2018 16:05:14 GMT

=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?

Re: Pass multiple values using if statement

Anonymous — Tue, 04 Dec 2018 17:31:22 GMT

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.