Inside field calculation?

[#1]

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

[swuehl]

Very well done Michael!

Could you explain why passing the string to evaluate via a variable makes a difference here? Or is this something we have to accept as invented by QlikTech engineers?

Regards

the other Stefan

[johnw]

Here's another working solution for arbitrary calculations.  I suspect that the performance would be higher than the looping solution, though I've done no testing on large data sets to prove it.  Also, it's dangerous to allow something like this with no validation of the calculations, as any data entry error by the user can crash out the script.  Validating calculations is a huge and I'd say separate topic.  Hopefully that's being done on the data entry side, which is where I think it belongs.  Calculations must of course follow QlikView's syntax and be possible to evaluate at script execution time, so in that sense, they aren't entirely arbitrary.

Data:
LOAD *
,recno() as ID // you can just use the key if your record has a key
INLINE [
F1, F2, Calc
1,  5, F1 + F2
4, 10, F1 * F2
15, 5, F1 / F2
];

Calcs:
LOAD
concat(chr(39) & Calc & chr(39),',',CalcSequence) as CalcNames
,concat(         Calc          ,',',CalcSequence) as Calcs
;
LOAD 
recno() as CalcSequence
,text(fieldvalue('Calc',recno())) as Calc
AUTOGENERATE fieldvaluecount('Calc')
;
LET vCalcs = 'pick(match(Calc,' & peek('CalcNames') & '),' & peek('Calcs') & ')';

DROP TABLE Calcs;

LEFT JOIN (Data)
LOAD
ID
,$(vCalcs) as Result
RESIDENT Data
;

EDIT: Swuehl, you could probably do something similar to pull off your solution with an arbitrary list of fields.  I suspect you could generate your list of replaces by detecting the field names using $Table and $Field.  I don't know if that would be simpler or more complex than the generated pick(match()).  One advantage is that an arbitrary list of fields may well have fewer members than an arbitrary list of calculations, and thus give you a shorter expression.  That might improve performance, and help avoid any expression length bugs if any were to surface.

[johnw]

Sunil Chauhan wrote:

did u see my post

You explicitly tie a row number to a calculation.  But as discussed, the calculations, the rows they will appear on, and even the possible fields are not something we know ahead of time.

[swuehl]

Hi John,

that's another nice solution without any evaluate() call, I need to start remembering the pick/match method using variables.

Since you are building a variable based on calc field values from all records, do you know of any limitiations regarding max variable length (or expression length of the expanded pick function)?

Stefan hasn't mentioned the common calc field expression length or expected number of records, so maybe that's not a problem anyway.

Regards,

swuehl

[johnw]

I'm not aware of any intended or published maximum variable or expression length.  That said, I've crashed out earlier versions of QlikView with huge expressions while specifically looking for bugs related to expression size.  I haven't tried to test for variable size bugs, but they could exist as well.

So it's one of those things that SHOULD work, and probably won't be a problem in practice.  BUT it might reasonably be expected to fail in some versions due to QlikView bugs if the variable/expression gets too big, whatever too big might be.

[rbecher]

Hi Stefan,

maybe this is a more suitable solution:

Calculations:

select ID, CALC from xyz_test;

LET rowText = NoOfRows('Calculations'); // get the total number of rows in Calculations table

for i=1 to $(rowText) // loop through every Calculations row

     let vCalc = FieldValue('CALC',$(i));

       let vId = FieldValue('ID',$(i));

     if len(trim('$(vCalc)')) > 0 then

         Results:

         select ID, FIELD1, FIELD2, $(vCalc) as RESULT from xyz_test where id = $(vId);

    end if

next

- Ralf

Some more explanation: This pushes the calculation into the database where it was edited. User could have use database operators or functions which could maybe unknown in QlikView. This also gives the possibility to use sub-selects or UDFs at the database level..

[swuehl]

This is a really nice evolving thread!

@Ralf: Some time ago I spent some hard hours debugging some strange behaviour. I traced it down to my usage of FieldValue, which - in a case like above - will not work as expected if your field values are non unique (i.e. multiple rows with same CALC expression). FieldValue is indexing the compressed column with unique values. You will notice the difference if you add one more row with repeated CALC expression.

So it might be better to use peek('CALC',$(i),'Calculations') with i running from 0 to NoOfRows-1.

A lot of solutions to one small problem!

Regards,

Stefan

[rbecher]

Good point Stefan (@swuhl), I didn't knew this! Is this a bug with FieldValue?

I made some adjustments to have a workaround:

Calculations:

select ID, CALC from xyz_test;

LET rowText = NoOfRows('Calculations'); // get the total number of rows in Calculations table

for i=1 to $(rowText) // loop through every Calculations row

     let vId = FieldValue('ID',$(i));

     let vCalc = peek('CALC',$(i) -1,'Calculations');

     if len(trim('$(vCalc)')) > 0 then

         Results:

         select ID, FIELD1, FIELD2, $(vCalc) as RESULT from xyz_test where id = $(vId);

    end if

next

- Ralf

Message was edited by: Ralf Becher 

..better to use peek here

[johnw]

John Witherspoon wrote:

Swuehl, you could probably do something similar to pull off your solution with an arbitrary list of fields.  I suspect you could generate your list of replaces by detecting the field names using $Table and $Field.  I don't know if that would be simpler or more complex than the generated pick(match()).  One advantage is that an arbitrary list of fields may well have fewer members than an arbitrary list of calculations, and thus give you a shorter expression.  That might improve performance, and help avoid any expression length bugs if any were to surface.

I decided to follow up on this.  With a little modification, it's simpler than my pick(match()) solution and works just fine.  I again expect performance to be high.  You could skip ID and Calc in the replace for slightly better performance at the cost of complexity.  And as mentioned, I think this variable expression is less likely to hit any bugs related to size.  See attached.  Script below.

Data:
LOAD *
,recno() as ID // you can just use the key if your record has a key
INLINE [
F1, F2, Calc
1,  5, F1 + F2
4, 10, F1 * F2
15, 5, F1 / F2
];

LET vCalcs = repeat('replace(',nooffields('Data')) & 'Calc';

FOR I = 1 TO nooffields('Data')
    LET vCalcs = vCalcs & ',' & chr(39) & fieldname(I,'Data') & chr(39) & ',' & fieldname(I,'Data') & ')';
NEXT

LEFT JOIN (Data)
LOAD
ID
,evaluate($(vCalcs)) as Result
RESIDENT Data
;

EDIT:  If you know your field list ahead of time, you can of course just put the replace in the initial load to save additional time.  That should be the normal case, I'd think, as I try to never load fields I don't know the name of.  Generating the expression like the above may still make sense from the standpoint of simplifying maintenance, though, as you then only have to list the fields in one place instead of two.  Of course, if people maintaining it later can't even understand what the loop and second load are doing, that might be an argument for just listing all the fields explicitly, but I don't personally tend to cater to people who can't read code, since the people maintaining this should be professional programmers.  I might not expect anyone to come up with this from scratch, but I'd expect them to be able to understand it, even if it took referencing QlikView documentation.

[Anonymous]

Stefan,

Apparently this is how it works.

BTW, in my version evaluate() is not needed.  Simply $(E) as Result is enough.

Same true about evaluate in the later versions, I assume.