topic Re: Multiple IF /AND + OR to create flag field in QlikView load script in QlikView

Multiple IF /AND + OR to create flag field in QlikView load script

johnmackintosh — Wed, 25 Nov 2020 16:16:04 GMT

Hi - first question, this feels like it should be obvious but its driving me mad now!

Would welcome a saner second pair of eyes.

My load script keeps failing and it's due the logic below.

I originally had 2 seperate flags, but wanted to combine the logic into 1 new one ( for easier charting - I couldn't get the set analysis to work to display the results of both in the same chart expression)

What I want to do (I know this is not the correct syntax but trying to make it straightforward to understand):

IF (condition 1)

AND ( condition 2)

AND (condition3)

AND (condition4) //(are true)

IF (condition 5)

AND (condition 6) //(are true)

THEN

dual('Yes',1),

//ELSE

dual('No',0)

AS FLAG

What I've tried in my script:

IF([Field1] Like 'condition1'

AND [Field2] Like 'condition2'

AND [Field3] = 0

AND [Field4] = 1

IF([Field5] = 0

AND [Field6] = 1,

Dual('Yes', 1),Dual('No',0))) AS [NEW_FLAG],

which results in:

Error in expression:

')' expected

I also tried:

IF([Field1] Like 'condition1' AND [Field2] Like 'condition2' AND [Field3] = 0 AND [Field4] = 1 , Dual('Yes', 1),

IF([Field5] = 0 AND [Field6]=1, Dual('Yes', 1),

Dual('No',0)))

as NEW_FLAG,

which resulted in the same

Error in expression:

')' expected

I can't see where the missing ')' is supposed to go? I've tried tagging one on the end but the syntax highlighter complains.

I've tried commenting out either/both of these sections and things run smoothly - this is definitely the section that is causing my script to fail.

I have searched around the community for ideas to write this properly but didn't find anything that I have been able to implement - happy to be pointed to a good resource though

Thanks

Re: Multiple IF /AND + OR to create flag field in QlikView load script

jpenuliar — Wed, 08 Nov 2017 17:18:25 GMT

Hi John,

try the formula :

If(((WildMatch([Field1],'condition1')) AND (Wildmatch([Field2],'condition2')) AND ([Field3] = 0) AND ([Field4] = 1)) OR (([Field5] = 0) AND ([Field6] = 1))

,Dual('Yes', 1)

,Dual('No',0)) as 'NEW_FLAG'

Re: Multiple IF /AND + OR to create flag field in QlikView load script

marcus_sommer — Wed, 08 Nov 2017 17:19:49 GMT

Try it this way:

IF(([Field1] Like 'condition1'

AND [Field2] Like 'condition2'

AND [Field3] = 0

AND [Field4] = 1)

([Field5] = 0

AND [Field6] = 1),

Dual('Yes', 1),Dual('No',0)) AS [NEW_FLAG],

- Marcus

Re: Multiple IF /AND + OR to create flag field in QlikView load script

jpenuliar — Wed, 08 Nov 2017 17:21:20 GMT

For complex equations, I normally start any formula/expressions like below:

If(('' AND '' AND '' AND '') OR ('' AND '')

,''

,'') as ''

then gradually fill in the '' with the conditions/parameters

Re: Multiple IF /AND + OR to create flag field in QlikView load script

johnmackintosh — Wed, 08 Nov 2017 20:49:09 GMT

Thanks you so much. I marked Jonathan's answer correct as he got there first( just!), but this also works - for the benefit of others this could equally have been marked the correct answer.

Re: Multiple IF /AND + OR to create flag field in QlikView load script

johnmackintosh — Wed, 08 Nov 2017 20:49:43 GMT

Thanks!

Tested and working great!