Evaluate formula expression inside Load statement

[#1]

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.

[giakoum]

did you try :

Result_Table:

LOAD

     SGROUP,

     $(FORMULA) as RESULTS;

LOAD

     SGROUP,

     FORMULA,

     AMOUNT

RESIDENT Sales;

[jerem1234]

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!

[#4]

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.

[#5]

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.

[giakoum]

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;

[#7]

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 ?

[giakoum · Accepted solution]

see attached

[berryandcherry6]

Hi,

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

Regards,

Supriya