How to concat different IF statements

[JgtzrSofia]

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

[henrikalmen]

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

where A and B are your avg-calculations. 

[rwunderlich]

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

[henrikalmen]

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"

[MarcoWedel]

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

[henrikalmen]

@MarcoWedel Impressive! 👏

[JgtzrSofia]

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

[henrikalmen]

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.

[rwunderlich]

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