topic IF AND in a calculated field in QlikView

IF AND in a calculated field

lisabarelle — Wed, 12 Jan 2022 14:07:42 GMT

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

Re: IF AND in a calculated field

maxgro — Wed, 12 Jan 2022 14:21:15 GMT

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

Re: IF AND in a calculated field

lisabarelle — Wed, 12 Jan 2022 14:51:51 GMT

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

Re: IF AND in a calculated field

maxgro — Wed, 12 Jan 2022 15:59:07 GMT

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/ScriptRegularStatements/Load.htm

Re: IF AND in a calculated field

lisabarelle — Wed, 12 Jan 2022 16:37:35 GMT

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