"And" statement not working in load script expression.

[#1]

Hi everyone

I'm new to Qlikview and i'm trying to put an expression in the load script. The expression is used as a scoring mechanism. I have broken it down line by line below to make it clearer. The problem is that any line of the expression that contains 'and' doesn't return a value. I'm clearly missing something obvious but for the life of my i can't work it out! Your help would be really appreciated!

if (len(Material_Name)>0 and len(litTitle)>0 and ApprovalStatus='Approved',10,

if (len(Material_Name)>0 and len(litTitle)>1,9,

if (len(Material_Name)>0 and ApprovalStatus='Approved',8,

if (len(Material_Name)>0,7,

if (len(litTitle)>0 and ApprovalStatus='Approved',6,

if (len(litTitle)>0 ,5,

if (ApprovalStatus='Approved',4,0 )))))))) as [Behaviour Score]

Thanks in advance to anyone who can help!!

Danny

[alexandros17]

The syntax seems correct, Try only a part of the expression at the time in order to verify if logic and data are correct.

es.

load

...

if (len(Material_Name)>0 and len(litTitle)>0 and ApprovalStatus='Approved',10,20) as test

...

let me know

[giakoum]

and is not the issue but maybe your data is. a sample application would help.

[#4]

Hi Daniel,

the syntax looks okay, so maybe have a look at the underlying values in your fields to make sure data is coming through as expected.

Might be lower case in ApprovalStatus for example.

hope that helps

Joe

[Anonymous]

I think it doesn't return values, becuase the if statement works only if all sets are disjoint...

For example the set len(litTitle)>0 and the set len(Material_Name)>0 and len(litTitle)>0 and ApprovalStatus='Approved' have an intersection...

Another user of this community had this problem..

KR

Elena

[agomes1971]

Hi,

if (len(Material_Name)>0 and len(litTitle)>0 and ApprovalStatus='Approved',10,

if (len(Material_Name)>0 and len(litTitle)>1,9,

if (len(Material_Name)>0 and ApprovalStatus='Approved',8,

if (len(Material_Name)>0,7,

if (len(litTitle)>0 and ApprovalStatus='Approved',6,

if (len(litTitle)>0 ,5,

if (ApprovalStatus='Approved',4,0

   )

   )

   )

   )

   )

   )

   ) as [Behaviour Score]

too much ) copy & paste the above... I think...

HTH

André Gomes

[maxgro]

I just copied your if....in text editor and

it seems to me there is a syntax error,

QlikView highlights in red the last parenthesis

Maybe you have to remove the last one

[agomes1971]

Hi,

have you solved your issue?

Thanks

André Gomes

[#9]

Thanks everyone for trying to help.

Massimo, You are right there was a syntax error in the script i shared with you, but i did notice this when i was playing around with the script and this was not the problem. I managed to get hold of our company's resident Qlikview expert who believed the issue  is to do with the fact that the following fields:

litTitle

Material_Name

Approval_Status

formTitle

Are loaded form separate tables which are then concatenated as so:

Name             Approval_Status      litTitle       formTitle          Material_Name

Geoff                   Approved          

Geoff                                            Example

Harry                    Approved          - -               - -                     - -

Harry                                           Example

Harry                                                            Example

Harry                                                                                     Example

For example the 'and' is trying to look along the row for Harry and missing the values in the litTitle, Material_Name, formTitle fields. (This is may very basic simplified understanding)

We are trying to do a 'group by' to overcome this problem.

I hope this makes sense to you all and thanks for your help everyone.

Danny

[#10]

I believe this is what Elana Pradoni was suggesting, am i correct??