topic Re: Expression in Load Script in QlikView

Expression in Load Script

Anonymous — Tue, 05 Jan 2016 00:20:18 GMT

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;

Re: Expression in Load Script

sunny_talwar — Tue, 05 Jan 2016 01:11:29 GMT

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

Re: Expression in Load Script

vikramv — Tue, 05 Jan 2016 01:13:44 GMT

Remove "Cashflow" in the group by.

Re: Expression in Load Script

Oleg_Troyansky — Tue, 05 Jan 2016 01:14:41 GMT

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!

Re: Expression in Load Script

Anonymous — Tue, 05 Jan 2016 01:22:51 GMT

Hi,

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

FILE;

Re: Expression in Load Script

petter — Tue, 05 Jan 2016 08:16:20 GMT

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.

Re: Expression in Load Script

Peter_Cammaert — Tue, 05 Jan 2016 08:25:17 GMT

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