Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
swatitomar
Contributor III
Contributor III

if Nested condition in Qlik

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

Labels (1)
2 Solutions

Accepted Solutions
martinpohl
Partner
Partner

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)

 

View solution in original post

Rohan
Creator
Creator

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.

View solution in original post

8 Replies
martinpohl
Partner
Partner

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
Contributor III
Contributor III
Author

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

martinpohl
Partner
Partner

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
Contributor III
Contributor III
Author

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

swatitomar_0-1595863211519.png

 

Kushal_Chawda

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

martinpohl
Partner
Partner

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

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

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.