Expression in a field

[#1]

Dear All,

I´m with a little difficulty about a field.

I have a table like bellow:

I want a way to do the FORMULA in script/expression that if I select the South Country the results is 2, if I select North the result should be 100 and if select America the result should be 102.

There is a function that do it ?

Best Regards,

LOAD * INLINE [

    COD, COUNTRY, VALUE, FORMULA

    1, BRAZIL, 10,

    2, COLOMBIA, 10,

    3, ARGENTINA, 10,

    4, USA, 10,

    5, MEXICO, 10,

    6, SOUTH, , (@1+@2-@3)/5

    7, NORTH, , (@4*@5)

    8, AMERICA, , "calc(6,7)"

];

[swuehl]

Hi Fabio,

I am not exactly sure what you are after.

If you are not set to the syntax of your expressions in your example, you might could use something like

LOAD COD, COUNTRY, FORMULA,

if(IsNull(evaluate(FORMULA))=-1,VALUE,evaluate(FORMULA)) as VALUE;

LOAD * INLINE [

   COD, COUNTRY, VALUE, FORMULA

   1, BRAZIL, 10,

   2, COLOMBIA, 10,

   3, ARGENTINA, 10,

   4, USA, 10,

   5, MEXICO, 10,

   6, SOUTH, , "(peek('VALUE',0)+peek('VALUE',1)-peek('VALUE',3))/5"

   7, NORTH, , "(peek('VALUE',3)*peek('VALUE',4))"

   8, AMERICA, ,"(peek('VALUE',5)+peek('VALUE',6))"

  ];

where I set strings with QlikView expressions as FORMULA and evaluate them during load.

(Note that index for peek starts with 0, I assume in your example @1 means 1. row)

Just an idea,

Stefan

[#3]

Dear Stefan,

Thanks for your answer bur unfortunatelly is not possible because this little table is only an exemple. In real table I have million rows and I can´t do it manually. I have to do in a automatic way.

Anyway, thanks again.

Best Regards,

Fabio Alamini.

[swuehl]

Hi Fabio,

where does the FORMULA syntax in your original post comes from?

As to my knowledge, there is no QlikView function that will just takes your FORMULA and evaluates it.

If the syntax is simple, you might be able to build a kind of parser in QlikView, but I don't think the effort is worth that (i.e. that's not trivial).

It looks like the FORMULA are pretty static so maybe all could be calculated before loading into QlikView with the appropriate tool (the one that takes the formula syntax).

If there are million of rows of data but only few (maybe repeating) FORMULA, you could try to rebuild the business rules behind the FORMULA inside QlikView (that requires that you have access to the rules, e.g. why and when to use "(@1+@2-@3)/5" for South. But as I understood, there are too many different FORMULAS to recode manually.

Sorry if this all is of no help,

Stefan