topic Re: Inside field calculation? in QlikView

Inside field calculation?

Fri, 11 Nov 2011 13:34:59 GMT

Hi all,

i have a table where one of the field contain claculation formula based on fields of the table

id, field1, field2, calc

1 , 1 , 10 , field1/field2

2 , 2 , 20 , field1*field2

3 , 3 , 30 , field1+field2

Is it possible to resident load this table in script and add new field which is based on calculation of "calc" field?

I've played with evaluate() function but the result is always null.

Thanks!

Stefan

Re: Inside field calculation?

SunilChauhan — Fri, 11 Nov 2011 14:09:01 GMT

see the attached file

where i have took the expression in resident table

tab1:
load * inline [
id, field1, field2
1 , 1      , 10
2 , 2      , 20
3 , 3      , 30
4 , 4      , 30
];

Load *,
if(rowno()=1,field1/field2,
if(rowno()=2,field1*field2,
if(rowno()=3,field1+field2,if(rowno()=4,field1-field2)))) as calc
resident tab1;

drop table tab1;

Inside field calculation?

Fri, 11 Nov 2011 14:12:50 GMT

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.

Thanks!

Stefan

Inside field calculation?

john_duffy — Fri, 11 Nov 2011 15:31:05 GMT

Hi Stefan.

How do you determine which fields are to be used in the calculation and what the calculations is?

In your sample in the first post, what determines that field 1 is divided by field 2 as opposed to multiplied by field 2 or added to field 2 on the first record?

John.

Re: Inside field calculation?

Fri, 11 Nov 2011 15:39:48 GMT

Hi John,

i don't determine nothinng. The "calc" field is a database field and the formula is stored in it.

Regards!

Stefan

Re: Inside field calculation?

swuehl — Sat, 12 Nov 2011 14:22:25 GMT

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.

Best regards,

another Stefan

Re: Inside field calculation?

Miguel_Angel_Baeyens — Sun, 13 Nov 2011 19:17:03 GMT

Hi Stefan,

What about using a variable with fields as parameters? Is that acceptable given your data model and possible values?

SET fxEval = Evaluate($1 & KeepChar($2, '+-/*') & $3);

LOAD *, $(fxEval(field1, calc, field2)) AS calc2 INLINE [
id, field1, field2, calc
1 , 1      , 10    , field1/field2
2 , 2      , 20    , field1*field2
3 , 3      , 30    , field1+field2
];

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Inside field calculation?

Sun, 13 Nov 2011 20:10:38 GMT

Thanks Stefan i've ednded with the same situation

Thanks Miguel using your solution actually show some results! But is there any way to adapt the variable to use randon number of fileds?

Thanks!

Stefan

Inside field calculation?

swuehl — Sun, 13 Nov 2011 20:25:41 GMT

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)'.

Hope his helps,

Stefan

Inside field calculation?

rbecher — Sun, 13 Nov 2011 20:47:07 GMT

Hi all,

why not store this in an Excel file and load the calc field?

😉

- Ralf

Re: Inside field calculation?

Sun, 13 Nov 2011 20:59:28 GMT

I've started with replacing method Stefan. Just to make it work but as far as i know my bosses they will offer the same qvw file to other of our clients and there the filed names are not totally the same. Thats why i'm looking for some solution that will save me some time in future

Ralf, is there any diff with loading from excel?!

Thanks!

Stefan

Re: Inside field calculation?

rbecher — Sun, 13 Nov 2011 21:22:37 GMT

Indeed, Excel is calculating the formulas, but you migh know..

Re: Inside field calculation?

rbecher — Sun, 13 Nov 2011 22:00:10 GMT

Btw. if you want to abstract (map) your qvw fields from the customer fields w/o changing the qvw file you could provide an external script (qvs via include) with the mapping which will be customized on the client side.

Inside field calculation?

swuehl — Sun, 13 Nov 2011 23:05:26 GMT

Ralf,

I haven't known that something plain like 'field1+field2' will be a correct excel expression indicating the sum of columns titled field1 and field2 row based. But I surely am no Excel expert.

Could you elaborate on that?

Stefan, where does your data come from? Could your data source somehow handle the calculation itself? I believe some probably could do.

Regards,

Stefan

Re: Inside field calculation?

rbecher — Mon, 14 Nov 2011 08:35:31 GMT

At this stage I clearly have to ask what is the use case? As I've understand so far it is the abstraction from a client´s datamodel. The calculated fields is only one approach as a workaround, right?

Inside field calculation?

SunilChauhan — Mon, 14 Nov 2011 08:44:49 GMT

tab1:

Load * inline [

id, field1, field2, calc 1 , 1 , 10 , field1/field2 2 , 2 , 20 , field1*field2 3 , 3 , 30 , field1+field2];

tab2:

Load * ,

if(Rowno()=1,field1/field2,if(Rowno()=2,field1*field2,if(Rowno()=1,field1+field2))) as Calc

residdent Tab1;

drop table Tab1;

tab1:

Load * inline [

id, field1, field2, calc 1 , 1 , 10 , field1/field2 2 , 2 , 20 , field1*field2 3 , 3 , 30 , field1+field2];

and in expression

if(Rowno()=1,field1/field2,if(Rowno()=2,field1*field2,if(Rowno()=1,field1+field2)))

Re: Inside field calculation?

Mon, 14 Nov 2011 08:45:47 GMT

Ralf,

the idea is that all the fields are loaded from the db. including the calculation field. All folrmulas are stored in this field. And for particular rows this formula must override the regular calculation. And the formula and fields inside it may vary and they are user defined and may be changed by the end user of the application any time.

Some developer gave the user option to enter this formulas and now the customer want to have this in qlikview and some charts to be made.

Stefan

Re: Inside field calculation?

rbecher — Mon, 14 Nov 2011 08:56:25 GMT

If there is no simple way in QV I would prefer to do this in the script with dynamic SQL. But to show how this could work I would need more details: the data model and the script.

- Ralf

Inside field calculation?

SunilChauhan — Mon, 14 Nov 2011 10:30:46 GMT

did u see my post

Inside field calculation?

Anonymous — Mon, 14 Nov 2011 21:19:16 GMT

Stefan,

This looks like working. Functionality is correct, but it could be cleaner, I think. Anyway, you can start from here and improve:

data:
LOAD *, rowno() as Row INLINE [
f1, f2, calc
1, 5, "f1 + f2"
4, 10, "f1 * f2"
15, 5, "f1 / f2"];

Let R=peek('Row');

for r=1 to $(R)

LET E=peek('calc', $(r)-1,'data');

tab:
LOAD
f1,
f2,
calc,
evaluate($(E)) as Result
RESIDENT data
WHERE Row=$(r);

Next r

DROP TABLE data;