Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inside field calculation?

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

32 Replies
Not applicable
Author

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
rbecher
MVP
MVP

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.

Astrato.io Head of R&D
swuehl
MVP
MVP

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

rbecher
MVP
MVP

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?

Astrato.io Head of R&D
SunilChauhan
Champion
Champion

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;

or

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

Sunil Chauhan
Not applicable
Author

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
SunilChauhan
Champion
Champion

did u see my post

Sunil Chauhan
Anonymous
Not applicable
Author

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;