topic Re: How to concat different IF statements in QlikView

How to concat different IF statements

JgtzrSofia — Sat, 25 Feb 2023 15:40:24 GMT

Hello!
I'm having a problem with trying to concat various if statements.

For example this two:

if(Avg(avgcong)>-2,'°C Cong','')

if(Avg(avgref)>10,'°C Refr','')

I want it to show like

°C Cong, °C Refr if both of them fulfill the requirements.

I tried using Concat() but it doesn't work because of Nested if, tried using &', '& and it shows invalid field

Re: How to concat different IF statements

henrikalmen — Sat, 25 Feb 2023 16:12:12 GMT

You need a third if statement that goes first. If(A and B, 'both', if(A…

where A and B are your avg-calculations.

Re: How to concat different IF statements

rwunderlich — Sat, 25 Feb 2023 18:20:24 GMT

How about:

if(Avg(avgcong)>-2, '°C Cong', '') & if(Avg(avgref)>10, ', °C Refr', '')

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: How to concat different IF statements

henrikalmen — Sat, 25 Feb 2023 20:54:48 GMT

That's a nice way of doing it, but If only the last if-statement evaluates to true the result will include a leading comma, like this:
, °C Refr

To avoid that, you could do it this way:

trim(replace(if(Avg(avgcong)>-2, '°C Cong ', '') & if(Avg(avgref)>10, ' °C Refr', ''), ' ', ', '))

From that you should get "°C Cong" or "°C Refr" or "°C Cong, °C Refr"

Re: How to concat different IF statements

MarcoWedel — Sun, 26 Feb 2023 21:09:15 GMT

maybe overcomplicating things, but just for the fun of it ... :

Pick(-2*(Avg(avgcong)>-2)-(Avg(avgref)>10), '°C Refr', '°C Cong', '°C Cong,°C Refr')

Re: How to concat different IF statements

henrikalmen — Mon, 27 Feb 2023 07:56:26 GMT

@MarcoWedel Impressive! 👏

Re: How to concat different IF statements

JgtzrSofia — Mon, 27 Feb 2023 18:33:41 GMT

the main problem is that I have 7 if statements to combine, adding them in a single if wouldn't be optimal

Re: How to concat different IF statements

henrikalmen — Mon, 27 Feb 2023 19:07:23 GMT

We can't invent a formula for you if we don't have all the facts. But it sounds like the formula rwunderlich gave you should work, just line up seven if-statements after each other. And you could wrap it in the trim- and replace functions I suggested.

Re: How to concat different IF statements

rwunderlich — Mon, 27 Feb 2023 19:14:19 GMT

It would be useful if you posted the complete requirement. Also, it typically makes sense to test from high to low when "everything below is true". eg

if(Avg(avgref)>10, ', °C Cong, °C Refr ,
if(Avg(avgcong)>-2, '°C Cong',
'whatever'
))

-Rob