if Nested condition in Qlik

[swatitomar]

HI Team,

I'm trying to write if condition on the basis of 3 fields.Getting incorrect value after applying the logic , what change i should implement.

if (Is_Cust='Yes', 'All Customer',
if (Is_Vndr = 'Yes' , 'All Vendor',
if (Is_Ptnr = 'Yes', All Partner',
if (Is_Cust='No',  'No Customer',
if (Is_Vndr = 'No',  'No Vendor',
if (Is_Ptnr = 'No', 'No Partner',
if (Is_Cust='Yes' and Is_Vndr = 'No' and Is_Ptnr = 'No',  'Only Customer',
if (Is_Cust='No' and Is_Vndr = 'Yes' and Is_Ptnr='No', 'Only Vendor',
if (Is_Cust='No' and Is_Vndr = 'No' and Is_Ptnr='Yes',  'Only Partner',
if (Is_Cust='Yes' and Is_Vndr = 'Yes' and Is_Ptnr='No',  'Customer & Vendor',
if (Is_Cust='Yes' and Is_Vndr = 'No' and Is_Ptnr='Yes',  'Customer & Partner',
if (Is_Cust='No' and Is_Vndr = 'Yes' and Is_Ptnr='Yes',  'Vendor & Partner',
if (Is_Cust='No' and Is_Vndr = 'No' and Is_Ptnr='No',  'None of Customer & Partner & Vendor',
if (Is_Cust='Yes' and Is_Vndr = 'Yes' and Is_Ptnr='Yes',  'Customer & Partner & Vendor'))))))))))))))) as Type;

I Have use crosstable for this above condition or by if i can get correct result.?

[martinpohl]

you have to change the logic in your if statement because if this is true

if (Is_Cust='Yes' , 'All Customer',

this 

if (Is_Cust='Yes' and Is_Vndr = 'Yes' and Is_Ptnr='No',  'Customer & Vendor',

will not be reached

Regards

[swatitomar]

Yes, This is the issue , how can i change my logic..Need help in the same

[martinpohl]

the easiest way is to reverse your if statement.

but I think in your statement, you want to identify more than one result.

customer: yes / no

vendor: yes / no

so, I need more information for the right statement

[swatitomar]

I have to build few Metrics on the basis of Type field. In the below chart , you can check 

[Kushal_Chawda]

In this case it is better to create calculated dimension in your chart itself instead of create it in script

[martinpohl · Accepted solution]

so, as I thought

if (Is_Cust='Yes', 'All Customer',
if (Is_Vndr = 'Yes' , 'All Vendor',
if (Is_Ptnr = 'Yes', All Partner',
if (Is_Cust='No',  'No Customer',
if (Is_Vndr = 'No',  'No Vendor',
if (Is_Ptnr = 'No', 'No Partner'  -> this is one category
if (Is_Cust='Yes' and Is_Vndr = 'No' and Is_Ptnr = 'No',  'Only Customer',
if (Is_Cust='No' and Is_Vndr = 'Yes' and Is_Ptnr='No', 'Only Vendor',
if (Is_Cust='No' and Is_Vndr = 'No' and Is_Ptnr='Yes',  'Only Partner',
if (Is_Cust='Yes' and Is_Vndr = 'Yes' and Is_Ptnr='No',  'Customer & Vendor',
if (Is_Cust='Yes' and Is_Vndr = 'No' and Is_Ptnr='Yes',  'Customer & Partner',
if (Is_Cust='No' and Is_Vndr = 'Yes' and Is_Ptnr='Yes',  'Vendor & Partner',
if (Is_Cust='No' and Is_Vndr = 'No' and Is_Ptnr='No',  'None of Customer & Partner & Vendor',
if (Is_Cust='Yes' and Is_Vndr = 'Yes' and Is_Ptnr='Yes',  'Customer & Partner & Vendor') -> this is a second category

theses two categories you can use in your chart (maybe in one chart with alternative dimension)

[martinpohl]

if you can, and in this case you can identify the category in script, for performance reasons it is better to calculate dimension information i script than in calculated dimension.

it's not possible at all (maybe top 5 customers, depending on selection) so it coul be an alternative.

[Rohan · Accepted solution]

Hi,

You can always use ApplyMap() to write a complex nested if condition :

Type_map :

Mapping Load 

Is_Cust&'-'&Is_Vndr&'-'&Is_Ptnr as Type_Key,

Type; 

Load * Inline [

Is_Cust, Is_Vndr,  Is_Ptnr , Type

Yes,No,No,'Only Customer'

Yes,No,Yes,'Customer & Partner'

Yes,Yes,No,'Customer & Vendor'

Yes,Yes,Yes,'Customer & Vendor & Partner'

No,No,No,'None of Customer & Partner & Vendor'

No,No,Yes,'Only Partner'

No,Yes,No,'Only Vendor'

No,Yes,Yes,'Vendor & Partner'

];

& Then use Applymap('Type_map', Is_Cust&'-'&Is_Vndr&'-'&Is_Ptnr, null()) as Type,

Also The All Customer, All Vendor & All Partner field values can't be created in this same field as their conditions overlap with some other conditions & you will get only one of those according to the order of conditions you have placed. You can create those values as a separate field & use them as a hierarchical drop down like

for eg : Transaction-Type >> Transaction.

Let me know if it worked for you.

Regards,

Rohan.