Expression in Load Script

[Anonymous]

Hello,

I am attempting to use the following expression in the load script. However, I am not sure how to approach the coding. The equation is as follows:

SQRT([pow(sum(Cashflow),2)]/[(sum(pow(Cashflow,2))*Cashflow)])*If(Cashflow<0, -1, 1)

So I now have the following script, however, I get the error 'invalid expression.'

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

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

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

FILE;

[sunny_talwar]

You don't need Cashflow Type and Cashflow in Group by. Try this:

LOAD Year,
Region,
Product,
[Product Type],
SQRT(pow(sum(Cashflow),2)/(sum(pow(Cashflow,2))*Cashflow))*If(Cashflow<0, -1, 1) as Cashflow,
'Mortality - Volatility Shock' as [Cashflow Type]
Group By Year, Region, Product, [Product Type];

LOAD Year,
Region,
Product,
[Product Type],
Cashflow
FROM FILE;

Update: Removed the square brackets based on Oleg's suggestion

[vikramv]

Remove "Cashflow" in the group by.

[Oleg_Troyansky]

I think you also need to replace your square brackets with parentheses, because square brackets make your calculations look like fields.

cheers

Oleg Troyansky

Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!

[Anonymous]

Hi,

So I now have the following script, however, I continue to get the error 'invalid expression'--but the grouping seems to work fine.

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

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

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

FILE;

[petter]

Change to this - see highligthed changes below:

LOAD

     Year,

     Product,

     [Product Type],

     [Cashflow Type],

     SQRT( ..... ) AS Cashflow

GROUP BY

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

LOAD

     ....

;

[Cashflow Type] needs to be in the GROUP BY list.

[Peter_Cammaert]

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...

Best,

Peter