topic Re: Brackets Hell! in QlikView

Brackets Hell!

dinicholls — Fri, 07 Nov 2014 11:32:12 GMT

Argh!

What am I doing wrong?!

I have the following expression:

if(sum([Line Debt])<=100, (sum([Line Debt])/100)*$(Cash Target - Line Debt <£100) ,

(SUM([Line Debt])/100)*$(Cash Target - Others))

Works fine, no problems.

I need to add the following, which on its own works fine as well:

if(left(AC2,2)='T8',

if(Month([Due Date])=Month(Today()), Sum([Line Debt]),(sum([Line Debt])/100)*$(Cash Target - Others)))

When I add the bold section, highlighted above, to the first expression everything goes wrong!

Where am I going wrong?!

if(sum([Line Debt])<=100, (sum([Line Debt])/100)*$(Cash Target - Line Debt <£100) ,

if(left(AC2,2)='T8', if(Month([Due Date])=Month(Today()), Sum([Line Debt]),

(SUM([Line Debt])/100)*$(Cash Target - Others))))

Confused!

Re: Brackets Hell!

dinicholls — Fri, 07 Nov 2014 11:42:27 GMT

HI,

Hmmmm, I might have to put it into the script instead of the expression. I have that many expressions, its getting a bit chaotic!

Re: Brackets Hell!

dinicholls — Fri, 07 Nov 2014 11:51:28 GMT

Hi,

In English, what I'm trying to do is:

If line debt < £100 sum line debt, divide by 100 and then multiply by cash target <100 variable, BUT

if AC2, left 2 = T8 AND Due date is in the current month then sum the line debt ELSE

sum the line debt, divide by 100 and multiply by cash target - other variable.

Do I need to write this another way? Or am I confusing myself and eveyone else?!

Thanks

Re: Brackets Hell!

kuba_michalik — Fri, 07 Nov 2014 13:00:38 GMT

No BUTs 😉

I mean, if the first condition

sum([Line Debt])<=100

is true, then the other ones are not even checked.

Re: Re: Brackets Hell!

morganaaron — Fri, 07 Nov 2014 13:15:51 GMT

Hi Diane,

Is the "BUT" condition you stated applicable if the line debt is <= 100 or applicable if NOT <=100?

If it's the former, I think you want to close the second if statement earlier, something like:

If(Sum([Line Debt]) <= 100,

if(left(AC2,2) = 'T8' and Month([Due Date]) = Month(Today()),

Sum ([Line Debt]),

(Sum([Line Debt])/100)*$(Cash Target - Line Debt <£100)),

(Sum([Line Debt])/100)*$(Cash Target - Others))

My question would be, what is the 'Line Debt <£100' text? Is that whole bracket (Cash Target - Line Debt..) a variable?

Hope that helps..!

Re: Re: Brackets Hell!

dinicholls — Fri, 07 Nov 2014 13:37:51 GMT

Hmmmmm,

Maybe I need to rearrange things?

This is the criteria that HAS to happen:

(if left AC2,2 = T8 AND the due date is in the current month, then sum the line debt) these HAVE to go together

if it not the above, but the line debt is less than 100 then sum line debt, divide by 100 and multiply by the variable 'Cash Target - <100'

If it doesn't fall in the above two, then sum the line debt, divide by 100 and multiply by 'Cash Target - Other' variable.

The data will fall into 1 of the above, but if it falls into 2 or more, it needs to be calculated in the order above.

Does that make sense?! Sorry, I keep looking at this at getting lost! I'm wondering if putting something in the script to identify the first criteria, and then referencing that in the expression might be an easier way to do things.

Thanks for all your help.

Re: Brackets Hell!

morganaaron — Fri, 07 Nov 2014 13:44:34 GMT

No problem, it can be confusing when you start to nest things.

From your logic, I'd suggest the following arrangement:

If(Left(AC2,2) = 'T8' and Month([Due Date]) = Month(Today()),

Sum([Line Debt]),

If(Sum([Line Debt]) <= 100,

(Sum([Line Debt])/100)*$(Cash Target - Line Debt <£100),

(Sum([Line Debt])/100)*$(Cash Target - Others)))

I do agree that it may be better to put flags in your load script to identify these conditions however! That would definitely make it easier to follow the lines of logic to get your outcome.

Aaron

Re: Brackets Hell!

dinicholls — Fri, 07 Nov 2014 14:25:48 GMT

The plot thickens!

Firstly, thank you, everyone for your help, it has helped no end!

I have now come across another hiccup, to do with the same problem!

I ended up adding a bit of script to identify the 'T8' records, so changed my expression to this:

if([T8 Check]='Yes', Sum([Line Debt]),

if(Sum([Line Debt])<100, (Sum([Line Debt])/100)*$(Cash Target - Line Debt <£100),

(Sum([Line Debt])/100)*$(Cash Target - Others)))

Except I'm finding that even though I have stated the first 'IF', when the one of the records meets the first IF, it is being overwritten by the second IF, if in fact the value is less than 100!!!!

I did try doing everything in script, and got the same result as above, hence me adding it to the script to see if it would sort the problem out.

I think the easiest way of sorting it, is to identify what records will sit where in the script, and add in new fields, then it will just be a matter of referencing them in the expression.

Thank you again, I've learnt a lot and wouldn't have got this far without your help!

Re: Brackets Hell!

morganaaron — Fri, 07 Nov 2014 14:34:50 GMT

Glad it's been of some help, what you're getting result wise sounds rather bizarre, it shouldn't evaluate any records that = 'Yes' to the T8 Check past the step if that is true - if you're comfortable doing so, you can attach an example of your .qvw and I'd be happy to take a look at it to see if I can spot any problems, if not I hope you find a suitable fix!

Regards,

Aaron

Re: Brackets Hell!

flipside — Fri, 07 Nov 2014 14:42:30 GMT

One method you could try when faced with complex nested ifs, is to split up the different calculations into smaller expressions then reference the expression label in the next expression. So for example for ...

if(sum([Line Debt])<=100, (sum([Line Debt])/100)*$(Cash Target - Line Debt <£100) ,

(SUM([Line Debt])/100)*$(Cash Target - Others))

1st expression is: (sum([Line Debt])/100)*$(Cash Target - Line Debt <£100) and labelled ColA

2nd expression is: (SUM([Line Debt])/100)*$(Cash Target - Others) and labelled ColB

3rd expression is: if(sum([Line Debt])<=100,[ColA],[ColB])

You can then hide the first two expressions when you've got the desired result or 'expand' the last expression by copying the earlier expressions over their labels. If you're using these calculations in multiple places, then it's worth looking into storing your expressions into variables so it becomes a single point of management.

flipside