Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

If then Else

Hi I am getting an expression error for this

if [ALCOHOL_Every.day] not like 'NA' then

[ALCOHOL_Every.day]

else 1=1

end if

What am I doing wrong here.

The values of ALCOHOL_Every.day ranges from 0 to 10 and I am seeing the extra value NA which I want to remove. What am I doing wrong in this ?

Thanks

Aj

1 Solution

Accepted Solutions
swuehl
MVP
MVP

There are multiple things to consider:

a) If..Then... Else..End if are control flow statements you can use in the script (not in the front end), but if you are using them in the script, you can't use them within a LOAD statement. Hence you can't really use a field like [ALCOHOL_Every.day] in the if condition.

b) If you want to negate a boolean, something like FIELD LIKE 'NA', you need to write NOT FIELD LIKE 'NA'

c) I don't really understand what 1=1 in your ELSE branch is made for.

So, depending where you want to remove the value 'NA', try something like this in the script:

LOAD

     If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day] ) as [ALCOHOL_Every.day],

     ...

or maybe (if you want to remove the complete record:

LOAD

     [ALCOHOL_Every.day],

     ...

FROM ...

WHERE [ALCOHOL_Every.day] <> 'NA';


In the front end, you can either use a calculated dimension like

= If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day] )

or set analysis in all your aggregations:

=Sum({<[ALCOHOL_Every.day] -= {'NA'}>} Value)

View solution in original post

4 Replies
sunny_talwar

Is this for a dimension? May be try this:

If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day], 1)

swuehl
MVP
MVP

There are multiple things to consider:

a) If..Then... Else..End if are control flow statements you can use in the script (not in the front end), but if you are using them in the script, you can't use them within a LOAD statement. Hence you can't really use a field like [ALCOHOL_Every.day] in the if condition.

b) If you want to negate a boolean, something like FIELD LIKE 'NA', you need to write NOT FIELD LIKE 'NA'

c) I don't really understand what 1=1 in your ELSE branch is made for.

So, depending where you want to remove the value 'NA', try something like this in the script:

LOAD

     If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day] ) as [ALCOHOL_Every.day],

     ...

or maybe (if you want to remove the complete record:

LOAD

     [ALCOHOL_Every.day],

     ...

FROM ...

WHERE [ALCOHOL_Every.day] <> 'NA';


In the front end, you can either use a calculated dimension like

= If([ALCOHOL_Every.day] <> 'NA', [ALCOHOL_Every.day] )

or set analysis in all your aggregations:

=Sum({<[ALCOHOL_Every.day] -= {'NA'}>} Value)

Not applicable
Author

Thanks Sunny / Swuehl,

I used the where clause to filter NA.

LOAD

     [ALCOHOL_Every.day],

     ...

FROM ...

WHERE [ALCOHOL_Every.day] <> 'NA';

Miles_Dyson
Contributor II
Contributor II

Hi Sunny, thank you for this code & all the others. Can you please explain what the number 1 does here? Thank you.