Lookup with two parameters - extra hard

[antonopn]

Hi everyone,

Please help! I am trying to calculate a growth of animals population (production) in a QlikView #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.

[Saravanan_Desingh]

Please upload the excel sheet

[antonopn]

uploaded in the initial post. many thanks!

[Saravanan_Desingh]

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

[Saravanan_Desingh]

Output:

[antonopn]

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

[antonopn]

Any proposal please? 😞

[Saravanan_Desingh]

So, my solution is not working?

[antonopn]

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.