IF AND in a calculated field

[lisabarelle]

Hi All,

I'm trying to create a new column in my table using the 'Calculated field'. It works fine with one condition ([RAG]), but when I try to use two other columns (i.e. two fields [RAG] and [CAUSE]) to create the output of the calculated filed, I don't get anything. I've tried the following, which doesn't work. Thoughts?

if(
     [RAG]='Green',
     'Green,
     if(
          (([RAG]='Red') AND ([CAUSE]='Cancelled')),

          'Cancelled',

          'Red'
     )
)

[maxgro · Accepted solution]

After the second Green add  '

LOAD
if(
[RAG]='Green',
'Green',
if(
(([RAG]='Red') AND ([CAUSE]='Cancelled')),

'Cancelled',

'Red'
)
) as NewField
,
*
;
LOAD * inline [
RAG, CAUSE
Red, -
Red, Cancelled
Green, -
Orange
];

[lisabarelle]

Thanks @maxgro . Do I put this is the expression field of the calculated field? If so, it doesn't seem to work.

Maybe if I better understand the syntax I can play around with it. What is the difference between LOAD and LOAD * inline? Do I need to hard code all of the combinations (I have more than the ones in this example)? And if so, do I need to do this every time I refresh my data?

Thanks

[maxgro]

You said in a table, I assumed you meant in the script (load) part of Qlik (QikView or Qlik Sense).

If instead you want to use in a chart as a Calculated dimension, try

if([RAG]='Green',  'Green',
if((([RAG]='Red') AND ([CAUSE]='Cancelled')), 'Cancelled',

'Red'))

Calculated dimension: if([RAG]='Green', ............

Expression CAUSE, RAG

You can use the expression (if.......) in the chart or in the script.

In the script Qlik add a field to the table in the data model during the refresh of the data. The field is calculated and available to all the charts because the field is in the data model.

In the chart Qlik calculates the value on the fly when you analyze the data. 

Regarding the load inline I often use load inline is a quick way to generate some data for test.

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegu...

[lisabarelle]

Thanks a lot! Now I get it - I removed the LOAD * in line and fiddled around and it works. Thanks!