topic Re: Case when equivalent in QlikView

Case when equivalent

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to convert the expression below for Qlik to understand.

case when SUM((CASE WHEN ((((TRIM(`FIELD1`) = 'VALUE1') AND (TRIM(`FIELD2`) = 'VALUE2')) AND (TRIM(`FIELD3`) != ‘VALUE3')) AND TRIM(`FIELD4`) IN ('VALUE4','VALUE5')) THEN 1 ELSE 0 END )) > 0 then

(

SUM((CASE WHEN ((((TRIM(`FIELD1`) = ' VALUE1') AND (TRIM(`Source`) = ' VALUE2')) AND (TRIM(`FIELD3`) != ‘VALUE3')) AND TRIM(`FIELD4`) NOT IN ('VALUE6', 'VALUE7')) THEN 1 ELSE 0 END ))

I have been using sum(if(match()) to perform the count but here having a condition of > 0 is giving me difficulties. But this is easy I guess.

Thank you in advance.

Re: Case when equivalent

swuehl — Tue, 25 Apr 2017 20:52:13 GMT

If() function seems to be an appropriate equivalent, in combination with a match(), just as you stated.

Note that you need to use three if() functions (you have three cases, two embedded in first)

Something like

=If(

Sum( If( Trim(FIELD1) = 'VALUE1' and Trim(FIELD2) ='VALUE2' and Trim(FIELD3) <> 'Value3' and Match(Trim(FIELD4), 'VALUE3','VALUE5'),1,0)) >0,

Sum(If (.....))

)

what's the issue with the >0 condition?

Re: Case when equivalent

Tue, 25 Apr 2017 21:01:51 GMT

Thank you Stefan, I had difficulties on syntax