topic Re: Syntax to evaluate a variable and show a text depending the value in QlikView

What is the syntax to evaluate a variable and show a text depending the value in Qlik View

MrBosch — Fri, 16 Sep 2022 09:35:43 GMT

Hello, the infinite pool of mighty wisdom,

Can anyone give me some tips on how I can make the following happen:

I am building a report. In the report, a variable needs to be evaluated and the corresponding textual content should be inserted.

An easy example: is the variable WEIGHT.

I create a New Text Object and need the variable to be evaluated as follows:

If WEIGHT is
< 50 texts should appear: 'You need to gain weight. You can achieve this ...'
50 - 75 'Great weight. Keep a healthy lifestyle.'
>75 'You should lose some weight. You could consider ...'.

Thanks for your help! Highly appreciated.

Re: Syntax to evaluate a variable and show a text depending the value

marcus_sommer — Fri, 10 Jun 2022 11:58:00 GMT

Maybe something like: if(WEIGHT < 50, 'a', if(WEIGHT > 75, 'c', 'b'))

- Marcus

Re: Syntax to evaluate a variable and show a text depending the value

MrBosch — Fri, 10 Jun 2022 13:58:22 GMT

Thanks Marcus. Great solution. Perhaps one more addition: now the test is <50 A, >75 B and else (between 50-75) C.

What would be the syntax if someone wants to add more conditions? So... to get 'D'?

So:
WEIGHT <25 'A'
WEIGHT 25-30 'B'
WEIGHT 30-35 'C'
WEIGHT >35 'D'

Re: Syntax to evaluate a variable and show a text depending the value

marcus_sommer — Mon, 13 Jun 2022 07:30:39 GMT

In general quite similar - and if the order - ascend or descend - of the check is continuously you don't need to check for between. This means the following should be working:

if(WEIGHT < 25, 'a', if(WEIGHT < 30, 'b', if(WEIGHT < 35, 'c', 'd')))

- Marcus

Re: Syntax to evaluate a variable and show a text depending the value

MrBosch — Tue, 14 Jun 2022 07:59:38 GMT

Thanks Marcus, the order might have been a problem.

Perhaps for others: this is the solution I have implemented:

=If ([BMI] < 18.5, 'Text for BMI less than 18.5'
,If ([BMI] >= 18.5 and [BMI] <25, 'Text for BMI between 18.5 and 25'
,If ([BMI] >= 25 and [BMI] <30, 'Text for BMI between 25 and 30'
,If ([BMI] >= 30 and [BMI] <90, 'Text for BMI BMI above 30'
,''
))))

So I leave the ELSE (row 5 in the code above) out and define all possible conditions. If you don't select a single occurence it will then not show the ELSE text (which I did see initially when the whole population had an average BMI of 25.7).

Also: don't use a ' in the Text area... (Yup made that mistake too and couldn't initially figure out why the expression gave an error. You can solve this by the escape sequence double quote: ''

Hope this helps someone someday.