topic Re: Nested Ifs in QlikView

Nested Ifs

Anonymous — Thu, 04 Oct 2018 00:17:50 GMT

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.

Re: Nested Ifs

BalaBhaskar_Qlik — Thu, 04 Oct 2018 01:34:47 GMT

Try like this:

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

Re: Nested Ifs

BalaBhaskar_Qlik — Thu, 04 Oct 2018 01:35:08 GMT

This:

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

Re: Nested Ifs

marcus_sommer — Thu, 04 Oct 2018 06:48:36 GMT

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

Re: Nested Ifs

MarcoWedel — Fri, 05 Oct 2018 19:41:45 GMT

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

Re: Nested Ifs

Anonymous — Sat, 06 Oct 2018 15:14:40 GMT

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])

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.

Re: Nested Ifs

Anonymous — Sat, 06 Oct 2018 15:18:23 GMT

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

Re: Nested Ifs

marcus_sommer — Sat, 06 Oct 2018 16:03:49 GMT

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

Re: Nested Ifs

MarcoWedel — Sat, 06 Oct 2018 18:52:58 GMT

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

Re: Nested Ifs

Anonymous — Sun, 07 Oct 2018 17:09:53 GMT

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

Re: Nested Ifs

Anonymous — Sun, 07 Oct 2018 17:11:02 GMT

Thanks for your help.