Evaluate() function on formula in table field during load

[dirk_fischer]

Hi community,

I struggle with the attached load script, which is not working as expected although I tried to base it upon the example in the online help.

For some reason the Evaluate(FORMULA) returns 0 for the first line and Null for all following lines, so it looks like it does not evaluate the content of the field FORMULA as proper formula.

TEST:
Load
Evaluate(FORMULA) As EVALUATED,
*
Inline [FORMULA, FIELD01, FIELD02
'RangeSum(FIELD01, FIELD02)', 1, 2
'RangeMax(FIELD01, FIELD02)', 1, 2
'RangeMin(FIELD01, FIELD02)', 1, 2
FIELD01 * 3, 1, 2
];

Does anybody have an idea, what is wrong in this code? Or is this a limitation of the Evaluate() function?

Best regards,

Dirk

[marcus_sommer]

I think the reason why it didn't work is that the specified fields within FORMULA remain strings within the evaluate() and aren't taken as field-reference. This means you need a more complex data-structure with more or less efforts (conditions) to combine the parts properly. The following worked and demonstrate what's meant:

TEST:
load *, evaluate(if(len(Function), Function & Parameter1 & ', ' & Parameter2 & ')', Parameter1 & Operator & Parameter2)) as X;
load * inline [
Function, Operator, Parameter1, Parameter2
rangesum(, , 1, 2
, +, 3, 4
];

I could imagine there are easier ways to solve your task as with such evaluate() approach ...

[dirk_fischer]

Hi Marcus,

thank you very much for your input. My problem is, that I try to build a parameterized checklist, where the target duration for each item is depending on different parameters (sometimes up to 4 or 5) and different functions. There may also be more than one function, so building the formula for the evaluate on the fly seems really to be a bit complex.

In the moment, I loop through my table, because it has less than 100 datasets, so the loop takes nearly no time. Then I can read the formula for each dataset into a variable and "execute" the formula during another load.

Not very elegant, but it works. If you have another idea how to solve such a problem in a more elegant way, I will be very happy for your input.

Best regards

Dirk

[marcus_sommer]

Just with this information it's difficult to suggest something. Thinkable would be to use a parametrized variable like:

set var = "pick(match($1, 1,2,3),
                        rangesum(...),
                        rangemax(...(),
                        ...)";

and then within the load:

load *, $(var(MetricID)) as Check from Source;

Further possibilities may be to apply the range-functions for all measure-fields - assuming the non-relevant ones are NULL or zero and may noch impact the results - or to transform the apparently crosstable structure into a stream-data structure or ... 

[dirk_fischer]

Hi Marcus,

thank you very much for your input, I will give it a try later today. But it might work in combination with what you suggested in your first response.

Best regards,

Dirk

[barnabyd]

G'day @dirk_fischer,

I had another idea of how to achieve this ...

TEST:
Load
Evaluate(replace(replace(FORMULA,'F1',FIELD01),'F2',FIELD02)) As EVALUATED,
*
Inline [FORMULA, FIELD01, FIELD02
'RangeSum(F1, F2)', 1, 2
'RangeMax(F1, F2)', 1, 2
'RangeMin(F1, F2)', 1, 2
F1 * 3, 1, 2
];

However, I don't think it is very elegant 🙁.

Cheers, Barnaby.

[dirk_fischer]

Good morning Barnaby

thank's a lot for this proposal. Unfortunately it does not help me, because it would mean I have to "hard-code" the replace structure with the field names. And that's something, I want to avoid.

Best regards

Dirk

[barnabyd]

G'day Dirk,

I think I don't have a clear idea of what your end goal is. In the final app, where are you loading the expressions from and where are the values coming from? At some point you need to know which fields to substitute into the parameters of the functions. 

Cheers, Barnaby.

[dirk_fischer]

Good morning Barnaby

I build a check list for make-ready processes, where each item of the list represents a task to do during the make-ready with an individual duration. And I want to calculate a target duration, depending on parameters of the individual task (e.g. for one job you have to mount 4 printing plates, the next time 5 printing plates, so it takes longer to do it).

I read the task as well as the formula for the calculation from an Excel sheet, so I read the formula at the same time as I read the task. In another application, I do the same thing and read the formula for the calculation into a variable, which is then applied to all datasets.

And this is in my opinion the major difference to what I try to do for this make-ready checklist. For the make-ready checklist, I try to apply a different formula to each dataset.

In principle it's like using a cooking recipe. If you want to feed 4 people you need 4 eggs and if you want to feed 10 people you need 10 eggs.

And I have 8 different type of make-ready processes I need to create this checklist for, so hardcoding is not an option.

I hope, this makes it a little bit clearer what I try to do, although I don't know, if there is a way to do this without looping through the datasets.

Best regards

Dirk

[marcus_sommer · Accepted solution]

I think in general you could remain by your approach of defining individual expressions but not with evaluate() because like above mentioned the specified fields aren't recognized as references else treated as strings (maybe there would be ways if not complete expressions are used else combined ones from multiple string + field parts ... whereby I doubt that it would be really expedient).

The essential point by each evaluation is the context where and how a statement is created and also called and here the logic could be moved to a variable which is called by another variable to include the calculation within a conditional pick-function. This may look like:

t0: load *, recno() as RecNo Inline [FORMULA, FIELD01, FIELD02
"RangeSum(FIELD01, FIELD02)", 1, 2
"RangeMax(FIELD01, FIELD02)", 1, 2
"RangeMin(FIELD01, FIELD02)", 1, 2
FIELD01 * 3, 1, 2
];

t1: load concat(FORMULA, ',',RecNo) as Formula resident t0;

let expr = peek('Formula', 0, 't1');
set var = "pick($1,$(expr))";

t2: load *, $(var(RecNo)) as EVALUATED resident t0;
drop tables t0, t1;

If within your final model the RecNo index couldn't be applied the logic could be extended to a pick(match()) to match the evaluation-type-id with the related expression.