Nested Ifs

[Anonymous]

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.

[balabhaskarqlik · Accepted solution]

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]

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]

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]

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