topic Evaluate formula expression inside Load statement in QlikView

Evaluate formula expression inside Load statement

Mon, 10 Feb 2014 13:12:02 GMT

Hello,

i'd like to calculate formula results into the loading script.

I have the following source table:

Sales:

LOAD * INLINE [

KPI, SGROUP, AMOUNT, FORMULA

KPI1, GROUP1, 10, SUM(AMOUNT + 1)

KPI2, GROUP1, 30, SUM(AMOUNT + 1)

KPI3, GROUP1, 40, SUM(AMOUNT + 1)

KPI4, GROUP2, 20, SUM(AMOUNT + 1)

];

I want to evaluate the formula inside the load script to be able to perform further validations with others values and data coming from other sources.

So, i try to calculate the formula resutl like this :

Result_Table:

LOAD

SGROUP,

evaluate(FORMULA) as RESULTS;

LOAD

SGROUP,

FORMULA,

AMOUNT

RESIDENT Sales;

But Evaluate don't give me any result.

Thanks a lot in advance for your help.

David.

Re: Evaluate formula expression inside Load statement

giakoum — Mon, 10 Feb 2014 13:14:25 GMT

did you try :

Result_Table:

LOAD

SGROUP,

$(FORMULA) as RESULTS;

LOAD

SGROUP,

FORMULA,

AMOUNT

RESIDENT Sales;

Re: Evaluate formula expression inside Load statement

jerem1234 — Mon, 10 Feb 2014 13:36:20 GMT

Hi David,

If you know that every record has the same expression for it, then you can store it into a variable from the last record, then evaluate it, like:

Sales:

LOAD * INLINE [

KPI, SGROUP, AMOUNT, FORMULA

KPI1, GROUP1, 10, SUM(AMOUNT + 1)

KPI2, GROUP1, 30, SUM(AMOUNT + 1)

KPI3, GROUP1, 40, SUM(AMOUNT + 1)

KPI4, GROUP2, 20, SUM(AMOUNT + 1)

];

Let vFormula = peek('FORMULA');

Result_Table:

LOAD

SGROUP,

$(vFormula) as RESULTS

RESIDENT Sales

Group by SGROUP;

But if you are gonna use sum, you'll need the group by clause for it. So if it changes, it might screw your load script up.

If the records don't have the same expression, I don't believe you can evaluate a dynamic expression like that in the script for one field.

Hope this helps!

Re: Evaluate formula expression inside Load statement

Mon, 10 Feb 2014 13:42:04 GMT

Yes, i ried that but the error is not really clear for me.

Here is the message :

Syntax error, missing/misplaced FROM:

Result_Table:

LOAD

SGROUP,

as ResultValue

The message sound like if the $ is not understood by the script editor.

Re: Evaluate formula expression inside Load statement

Mon, 10 Feb 2014 13:48:34 GMT

The formulas are never the same (the example is a simplification of what i really have).

About the fact that i can't use Evaluate() inside a load statement, i really don't know, but i'm sure i didn't find the way to use it if it's possible. That's why i'm looking for confirmation and perhaps workaround.

Thanks for your feedback.

Re: Re: Evaluate formula expression inside Load statement

giakoum — Mon, 10 Feb 2014 13:48:36 GMT

this works for me (see attached):

Sales:

LOAD * INLINE [

KPI, SGROUP, AMOUNT, FORMULA

KPI1, GROUP1, 10, SUM(AMOUNT + 1)

KPI2, GROUP1, 30, SUM(AMOUNT + 1)

KPI3, GROUP1, 40, SUM(AMOUNT + 1)

KPI4, GROUP2, 20, SUM(AMOUNT + 1)

];

let vFormula = fieldvalue('FORMULA', 1);

Result_Table:

LOAD

SGROUP,

$(vFormula) as RESULTS

RESIDENT Sales

Group by SGROUP;

Re: Evaluate formula expression inside Load statement

Mon, 10 Feb 2014 14:08:35 GMT

Thanks,

effectively, if the formula is the same, the solution works fine.

But in my case, formulas are (in reality) different for each KPI.

I will see how to decline the solution using a loop over table rows.

My concerns will perhaps become the efficiency of the loop over fact table and millions rows ...

Is someone able to confirm that EVALUATE() function cannot be use inside a LOAD statement ?

Re: Re: Evaluate formula expression inside Load statement

giakoum — Mon, 10 Feb 2014 14:39:35 GMT

see attached

Re: Evaluate formula expression inside Load statement

berryandcherry6 — Wed, 19 Oct 2016 06:36:29 GMT

Hi,

can you share code of this solution, since i am using qliksense.

Regards,

Supriya