topic Re: Script calculation for growth of animals based on a model in QlikView

Lookup with two parameters - extra hard

antonopn — Tue, 16 Feb 2021 16:13:50 GMT

Hi everyone,

Please help! I am trying to calculate a growth of animals population (production) in a #script. We have the first day real weight and we need to forecast in the future the estimated growth (New Weight), as you see on the photo1.

There is in another table, a growth model which consists in two variables.
1) Temperature
2)weight range,
This table is linked with the "growth model ID" to the "population" of the first table.

New weight = previous date weight + (previous date weight*growth factor)

Here is the hard part. How can we determine the growth factor, looking up on the 2nd table? It is based on round(temperature) and the weight range. For example in the first case, with round(temperature)=14 we pick the red line and growthfactor=0,119. In the second case with round(temperature)=13 we pick the blue line. The weight is not always matching the "model", it is in a range! We have to select the first row where "weight">=model weight.

Leaving an excel file with data in the attached files.

If anyone could help would be great. I could put an initiative of some 20$ (Paypal transfer) to anyone who solves, should this be OK with the community's policy.

Re: Script calculation for growth of animals based on a model

Saravanan_Desingh — Sun, 14 Feb 2021 15:05:25 GMT

Please upload the excel sheet

Re: Script calculation for growth of animals based on a model

antonopn — Sun, 14 Feb 2021 15:19:20 GMT

uploaded in the initial post. many thanks!

Re: Script calculation for growth of animals based on a model

Saravanan_Desingh — Sun, 14 Feb 2021 16:13:44 GMT

Try this,

New Weight=FirstSortedValue([Growth Factor],fAbs(Temperature-[Model Temperature])+fAbs(Weight-[Model Weight]))*Weight

Growth Factor=FirstSortedValue([Growth Factor],fAbs(Temperature-[Model Temperature])+fAbs(Weight-[Model Weight]))

Re: Script calculation for growth of animals based on a model

Saravanan_Desingh — Sun, 14 Feb 2021 16:14:22 GMT

Output:

Re: Script calculation for growth of animals based on a model

antonopn — Sun, 14 Feb 2021 16:32:31 GMT

Looks close, thank you! Actually the field weight is not used. I should ommit it. We need to start from yellow "new weight" and loop. But if your "lookup" function works, maybe I can do the rest with bested loops and "peek". Will let you know

Re: Script calculation for growth of animals based on a model

antonopn — Mon, 15 Feb 2021 18:54:10 GMT

Any proposal please? 😞

Re: Script calculation for growth of animals based on a model

Saravanan_Desingh — Mon, 15 Feb 2021 19:43:31 GMT

So, my solution is not working?

Re: Script calculation for growth of animals based on a model

antonopn — Mon, 15 Feb 2021 19:46:40 GMT

No. I answered above. But thank you so much for your effort! Found a way to do the looping though. Seems the only problem now is how to lookup with two parameters on the script, based on a previously loaded table.