topic Re: Execute the formula in a field in QlikView

Execute the formula in a field

Mon, 28 Jan 2013 13:50:01 GMT

Hi All:

We have all the formulas in one table with sequence no in which it has to be executed. I tried and able to combine the smaller piece of formula into bigger one and made it available as part of data. Now i am not able to execue the formula.

I have also attached the sample app for your reference

Pls let me know if you have any answer.

Thanks in advance.

Re: Execute the formula in a field

Mon, 28 Jan 2013 13:57:05 GMT

FYI..I need to execute at script level rather in expresession.

Re: Execute the formula in a field

RedSky001 — Mon, 28 Jan 2013 16:22:45 GMT

It was difficult following your script as you have not provided the data you are loading.

Anyway I had a go at it, basically just replacing the field names in the formula with the actual values. let me know if this helps...

// This just loads one row with "IF(C1>C2, 'Y',IF(C1=C2,'S',IF(C1=0,'N')))" in it.

TEMP:

LOAD Formula

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

let vFormula = peek('Formula',0,'TEMP');

DROP TABLE TEMP;

TEMP:

LOAD * Inline [TY,C1,C2

A,2,1

B,1,1

C,1,1

D,0,2

];

COUNT:

LOAD COUNT(TY) AS X

Resident TEMP;

vRows = PEEK('X',0,'COUNT');

DROP TABLE COUNT;

for i=0 to vRows - 1

let vTY = PEEK('TY',$(i),'TEMP');

let vC1 = PEEK('C1',$(i),'TEMP');

let vC2 = PEEK('C2',$(i),'TEMP');

let vFormula_new =Replace(vFormula,'C1','$(vC1)');

let vFormula_new2 =Replace(vFormula_new,'C2','$(vC2)');

LET vFormula_new3 = $(vFormula_new2);

// NoConcatenate I want it to concatnate so leave as it

OUTPUT:

LOAD * Inline [TY,vC1,vC2,Formula,Cat

$(vTY),$(vC1),$(vC2) ,$(vFormula),$(vFormula_new3)

];

next

DROP TABLE TEMP;

Re: Execute the formula in a field

Mon, 28 Jan 2013 16:47:50 GMT

Pls find the data for your reference. Meanwhile i will lookinto it.

Re: Execute the formula in a field

RedSky001 — Mon, 28 Jan 2013 17:40:27 GMT

The problem seems to be with the quotes.

For example

,EVALUATE( IF(C1>C2, 999 ,IF(C1=C2,555,IF(C1=0,000))) ) as Category2

will return values

I don't have time to play around and make this work but hopefully that's of some help.

Re: Execute the formula in a field

Tue, 29 Jan 2013 10:42:52 GMT

Thanks. I have resolved the issue.

Replaced my Evaluate function with your logics. It's working fine. Pls find the application and excel file for your reference.

Re: Execute the formula in a field

RedSky001 — Tue, 29 Jan 2013 10:45:02 GMT

Looks good, would you mark my post as correct or helpfull please.

Mark