Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Indeed, Excel is calculating the formulas, but you migh know..
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.
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
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?
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)))
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
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
did u see my post
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;