Inside field calculation?

Hi Sunil,

thanks for your answer but i'm looking for more general solution (if is possible) because the table in the first post is a sample table and the real data may contain many and different calculations and many fields included.

Re: Inside field calculation?

Stefan,

that's an interesting question and I was attempted to say, yes you can do this using evaluate() function.

I remembered I played around with something similar few weeks ago (http://community.qlik.com/message/135222) and I just wanted to adapt that to your requirements. I couldn't make it to work again. Then I noticed I was now using QV10SR3 and I remembered using QV9 from time to time - and surprisingly, my linked demo script just works fine in QV9SR6.

Then I continued working on QV9, trying to integrate your requirements. To my surprise, I couldn't get some assumingly easy expressions like 'field1' to work either, it always return NULL, as you mentioned.

I could access the data for already read in rows by using the peek() method, but this is not what you required. Hm, why this? Is the expression evaluated in a different context where the actual current input row fields are not accessible, like in a global context?

I tried to investigate on this, but couldn't find any information in the help or the forum. Almost all examples are using quite easy expressions where they don't use current input row field values, most of the time examples like '6*8' or something like this.

I then noticed that the problem might be similar to http://community.qlik.com/thread/15247 and the statement Hector made. Not sure about this, I believe it has to do with QV's internal structure (latter issue probably with how and when expressions are (pre-)parsed and evaluated, which I believe will probably not be done on record base).

Then I played around with other functions, trying to evaluate() them on script load, something like:

Table2:

LOAD id, field1, field2, calc,

if(IsNull(evaluate(calc))=-1,'no',evaluate(calc)) as Result;

LOAD * INLINE [

id, field1, field2, calc

1 , 1 , 10 , field1/field2

2 , 2 , 20 , field1*field2

3 , 3 , 30 , field1+field2

4 , 4 , 40 , date(today())

5 , 5 , 50 , "peek('field1',-1)*peek('field2',-1)"

7 , 7 ,60 , rowno()

8 , 8 , 70 , rowno()*field1

];

and I again noticed differences between QV 9 and 10, not only with the peek() function evaluation, but also with the rowno().

Finally, I was just about giving up, I remembered the "easy ones" using something like

evaluate('8*6')

so what about replacing the field values in the string to evaluate before evaluating?

And in fact, I got this to work for both QV9 and QV10:

Table2:

LOAD id, field1, field2, calc,calc2,

if(IsNull(evaluate(calc2))=-1,'no',evaluate(calc2)) as Result;

LOAD id, field1, field2, calc,

Replace(Replace(calc,'field1',text(field1)),'field2',text(field2)) as calc2;

LOAD * INLINE [

id, field1, field2, calc

1 , 1 , 10 , field1/field2

2 , 2 , 20 , field1*field2

3 , 3 , 30 , field1+field2

];

Not sure if this meets your requirement, but I think I am at my wits' end.

Inside field calculation?

Stefan,

the replace method will also work with more than two fields and if not every field is used in the calc string (the replace just won't have any effect for that field). So just extend the expression with more replace() functions up to maximum number of fields you need (well, I hope you are not using too many, and honestly I don't know if restrictions apply for too many functions embedded into other functions, just give it a try.

I think replacing the field values is probably more flexible with regard to calc function structure, for example you will have not problems with such 'sqr(field1)' or 'field2 / field1' or 'field1 / (field2 + field1)'.