Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
johnmackintosh
Contributor III
Contributor III

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


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)

OR

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

                         OR

                              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

1 Solution

Accepted Solutions
jpenuliar
Partner - Specialist III
Partner - Specialist III

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'

View solution in original post

5 Replies
jpenuliar
Partner - Specialist III
Partner - Specialist III

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'

marcus_sommer
MVP & Luminary
MVP & Luminary

Try it this way:

IF(([Field1] Like 'condition1' 

                AND [Field2] Like 'condition2'

                    AND [Field3] = 0

                         AND [Field4] = 1)

                         OR

                              ([Field5] = 0

                                 AND [Field6] = 1),

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

- Marcus

jpenuliar
Partner - Specialist III
Partner - Specialist III

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

johnmackintosh
Contributor III
Contributor III
Author

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.

johnmackintosh
Contributor III
Contributor III
Author

Thanks!

Tested and working great!