Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Nested Ifs

I have a spreadsheet input file that I am trying to manipulate in the load script.  In the below script portion, I need to use the [Agency Name] as is or conditionally generate two new values being 'Cancelled Shift' or 'CL' respectively.

    IF ([External Agency] <> ' ' and ([Request Status]  = 'Not Fillable'), 'Cancelled Shift',[External Agency]) as [External Agency],

    IF ([External Agency] =  ' ' and ([Request Status]  = 'Filled'), 'CL',[External Agency]),

as written, the first IF statement is executed correctly but the second IF statement is ignored.  Any help would be much appreciated.

1 Solution

Accepted Solutions
balabhaskarqlik

Try like this:

IF (IsNull([External Agency]) and ([Request Status]  = 'Filled'), 'CL',[External Agency]) as [External Agency]

View solution in original post

10 Replies
balabhaskarqlik

Try like this:

IF (IsNull([External Agency]) and ([Request Status]  = 'Filled'), 'CL',[External Agency]) as [External Agency]

balabhaskarqlik

This:

IF (Match([External Agency],'') and ([Request Status]  = 'Filled'), 'CL',[External Agency]) as [External Agency]

marcus_sommer

You need to check which chars are really within your fieldvalues - any kind and numbers of spaces, empty string, NULL, ... to be able to query it properly. Nevertheless quite often worked the following:

if(len(trim([External Agency])) = 0, ... respectively if(len(trim([External Agency])) >= 1, ...

- Marcus

MarcoWedel

Contrary to your thread title your code defines two separate If() expressions instead of one nested If().

Can you post some sample values as well as your expected result to clarify?

thanks

regards

Marco

Anonymous
Not applicable
Author

Hi Marco:

You are right.  I do not know how to correctly format a "nested if".  So, how would I convert the following into a correct "nested if"?

IF ([External Agency]) <> ' ' and ([Request Status])  = 'Not Fillable'), 'Cancelled Shift',[External Agency]) as [External Agency],

IF ([External Agency] =  ' ' and ([Request Status]  = 'Filled'), 'CL',[External Agency]),

So if the value of ([External Agency] coming in was "Alpha" and ([Request Status]  = 'Not Fillable'), the value of ([External Agency]) would be written as "Cancelled Shift", else ([External Agency])

or

If the value of ([External Agency] coming in was "null" and ([Request Status]  = 'Filled'),

the value of ([External Agency]) would be written as "CL", else ([External Agency])


Thanks for your help.




Anonymous
Not applicable
Author

Hi Marcus:

Thanks for your reply.  Your response works but my other problem was how to correctly format a "nested if" as outlined in my response to Marco Wedel.

Much obliged.

David

marcus_sommer

Do you mean something like this:

IF(len(trim([External Agency])) = 0 and [Request Status]  = 'Not Fillable', 'Cancelled Shift',

IF(len(trim([External Agency])) >= 1 and [Request Status]  = 'Filled', 'CL', [External Agency])) as [External Agency],

  

which is:

if(condition, then, if(condition, then, else))

- Marcus

MarcoWedel

Thanks, guess I got it.

One possible solution should be something like:

If(Len(Trim([External Agency])) and [Request Status]='Not Fillable',

  'Cancelled Shift',

  If(not Len(Trim([External Agency])) and [Request Status]='Filled',

     'CL',

     [External Agency]

    )

  ) as [External Agency]


hope this helps


regards


Marco

Anonymous
Not applicable
Author

Thank you Marco.  It was the need for two ")' after line 5 of your response that I was missing.  It was hard to find a good example of nested ifs anywhere.  Regards

David