topic Re: Advanced Expression in QlikView Load Script in QlikView

Advanced Expression in QlikView Load Script

Anonymous — Tue, 05 Jan 2016 13:04:07 GMT

Hello,

I have asked this question before, however, it has still not been resolved.

I am trying to add an expression in the Load Script, but when I execute the script I continue to get the error 'invalid expression.' I am not sure how to proceed and could use some help:

Concatenate
LOAD Year,
Region,
Product,
[Product Type],
[Cashflow Type],
SQRT((pow(sum(Cashflow),2))/(sum(pow(Cashflow,2))*Cashflow))*If(Cashflow<0, -1, 1) as Cashflow

Group By Year, Region, Product, [Product Type], [Cashflow Type];

LOAD Year,
Region,
Product,
[Product Type],
Cashflow,
'Mortality - Volatility Shock' as [Cashflow Type]
FROM

File;

Re: Advanced Expression in QlikView Load Script

Peter_Cammaert — Tue, 05 Jan 2016 14:29:00 GMT

Then I'll copy my reply from the previous discussion.

And this one may cause your problem as well, since these occurrences of Cashflow are not listed in the GROUP BY clause and not embedded in an aggregation function:

.../(sum(pow(Cashflow,2))*Cashflow))*If(Cashflow < 0, -1, 1) as...

Field Cashflow is used both inside and outside of an aggregation function. Can't do this in a query language as the query engine wouldn't know which value to take for the fields outside of an aggregation function.

The best thing you can do is put all occurrences of Cashflow (especially the sign check) inside the aggregation functions (both sum)

Best,

Peter

Re: Advanced Expression in QlikView Load Script

Peter_Cammaert — Tue, 05 Jan 2016 14:38:18 GMT

As an example, try to calculate the resulting row of this source table.

Year, Region,Product,Product Type,Cashflow

2015, North, Box1, Toy, -1000

2015, North, Box1, Toy, +5000

The GROUP BY clause will reduce these rows into a single one with these field values.

Year, Region,Product,Product Type,Cashflow Type, Cashflow

2015, North, Box1, Toy, Mortality - Volatility Shock, ???

But what should go into CashFlow? What will be the result of your Cashflow sign test if you've got both a positive as well as a negative value to choose from?

Peter

Re: Advanced Expression in QlikView Load Script

rubenmarin — Tue, 05 Jan 2016 14:55:06 GMT

I agree with Peter, the expression have some parts where expects many values of cashflow while other need an alone value, that will return the 'invalid expression' error.

This expression won't return an error but it's not what you want, I only set as an example:

SQRT((pow(sum(Cashflow),2))/(sum(pow(Cashflow,2))*Sum(Cashflow)))*If(Sum(Cashflow)<0, -1, 1) as Cashflow

Those 'Sum()' tells QV what to do with the different values, you'll need to think another way to calculate that, in example if your last 'if' is trying to convert the result to always positive you can enclose all the expression in a fabs() function:

Fabs(SQRT((pow(sum(Cashflow),2))/(sum(pow(Cashflow,2))*Sum(Cashflow)))) as Cashflow

Although it will return the positive of the resultant expression, not for each Cashflow value as your original expression tries to do, again, just an example of valid expressions.

Re: Advanced Expression in QlikView Load Script

Anonymous — Tue, 05 Jan 2016 15:38:58 GMT

Hi,

Thank you for clarifying your response; I was a little confused at first.

I now realize that my equation cannot be (easily) completed in the load script. I am going to look into Aggregated Set Analysis to try and use variables within the app itself.

Re: Advanced Expression in QlikView Load Script

Peter_Cammaert — Tue, 05 Jan 2016 16:22:08 GMT

Glad to be of assistance.

Re: Advanced Expression in QlikView Load Script

Peter_Cammaert — Tue, 05 Jan 2016 16:37:21 GMT

You can mark your two discussions as "Presumed Answered", just to make sure that the dicussions are closed. The Helpfull's can be assigned using the Actions menu entry in each reply.

Good luck.