Advanced Expression in QlikView Load Script

[Anonymous]

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;

[Peter_Cammaert]

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

[Peter_Cammaert]

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

[rubenmarin]

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.

[Anonymous]

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.

[Peter_Cammaert]

Glad to be of assistance.

[Peter_Cammaert]

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.