topic Re: user defined function to select values from a table for subsequent calculation in QlikView

user defined function to select values from a table for subsequent calculation

Fri, 09 Jan 2015 16:31:24 GMT

Hey,

I use this user defined function to calculate the water content of air. :

0.622 * (rel. hum * saturated vapour pressure) / (air pressure - rel. hum * saturated vapour pressure)

-->

SET udf_water_content = Num(0.622*($1*$2)/(($3-$1*$2)));

But now I would like to get the saturated vapour pressure of the measured temperature instead of a fix value.

water_content = $(udf_water_content(humidity,=$(VapPress(=$(round(temperature))),101300));

I have a table with temperatures from -20 to 99 °C with the associated saturated vapour pressures. So how can I define the VapPress function to select the vapour pressure of the associated temperature? Attached you can see an extract of the table with the preassures. The meassured temperatures are floats, so I would need to round them as well.

temp	saturated_vapour_pressures
17	1936,2
18	2062
19	2196
20	2337
21	2485
22	2642

Thanks for feedback.

Henrik

Re: user defined function to select values from a table for subsequent calculation

chrismarlow — Fri, 09 Jan 2015 16:56:30 GMT

You can embed If statements (and I guess other functions) in user defined functions. That said it did take me a while to get the brackets right on this one;

Re: user defined function to select values from a table for subsequent calculation

rwunderlich — Fri, 09 Jan 2015 19:10:13 GMT

The expression to select pressure for a specific temp would be:

Only({<temp={18}>}saturated_vapour_pressures)

and for a rounded value

Only({<temp={$(=round(18.2))}>}saturated_vapour_pressures)

So the function version would be:

Only({<temp={$(=round($1))}>}saturated_vapour_pressures)

The $() is going to give you some grief if you try to define as is with a SET statement. One workaround is to escape the $ using replace like this:

LET VapPress3 = replace(

'Only({<temp={@(=round($1))}>}saturated_vapour_pressures)'

,'@','$');

Another alternative to use another expression that doesn't use $().

SET VapPress = Only({<temp={"=temp=round($1)"}>}saturated_vapour_pressures);

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: user defined function to select values from a table for subsequent calculation

Fri, 09 Jan 2015 21:40:13 GMT

Hey Rob,

first of all I would like to thank you for your detailed reply! But I still got one issue. When I use your function to select the associated saturated vapour pressure it works with a manual entered temperature. It works as well when I insert the function in my first function.

Functions

SET udf_wc = Num(0.622*(($1*$2)/(($3-$1)*$2)));

SET udf_svp = Only({<vp_temp={"=vp_temp=round($1)"}>}saturated_vapour_pressures);

Correct result with following functions

=$(udf_svp(18.2))

=$(udf_wc(50,$(udf_svp(18.2)),101300))

But now it comes to diagramms. I use a table with measured data containing the columns "time", "temperature" and, "humidity".

If I choose "time" as the dimension, I can use the function "=$(udf_wc(humidity,2337,101300))" correctly. It also works, when I do this: "=$(udf_wc(humidity,$(udf_svp(20)),101300))". But if I try this "=$(udf_wc(humidity,$(udf_svp(temperature)),101300))" it doesn't work. But I absolutely have no clue why.

Thanks for any help!

Henrik

Re: user defined function to select values from a table for subsequent calculation

rwunderlich — Fri, 09 Jan 2015 23:53:18 GMT

There has to be a single temperature value for the row. If not, some type of aggregation function needs to be used like:

=$(udf_wc(humidity,$(udf_svp(max(temperature))),101300))

Could that be the issue?

-Rob

Re: user defined function to select values from a table for subsequent calculation

Sat, 10 Jan 2015 09:09:56 GMT

hey rob,

there exists a single temperature value for every row. Same as for humidity. So I don't get why it isn't working If I use the max() function, all of the calculation works with one temperature value (in my case 21°C). So the diagramm result shows the same curve as the rel. humidity curve and that is not correct.

henrik

Re: user defined function to select values from a table for subsequent calculation

rwunderlich — Sat, 10 Jan 2015 15:15:45 GMT

It sounds like you have more than one temp per time, or that time snd temp are not properly associated. Can you post a sample qvw?

Rob

Re: user defined function to select values from a table for subsequent calculation

Sat, 10 Jan 2015 17:08:19 GMT

Hey rob,

I am 100% sure I have a temp per time. For example if I do following it shows a curve as well:

=$(udf_wc(temperature,$(udf_svp(20,1)),101300))

Only in combination of both with time associated values it doesn't work ...

=$(udf_wc(humidity,$(udf_svp(temperature)),101300))

Ah and I just recently figgured out that it also don't work if I do following:

=$(udf_wc(20,$(udf_svp(humidity)),101300))

So the bug must be somewhere in the function "udf_svp" ... just for the records, I used this one (my columns names are "vp_temp" and "saturated_vapour_pressures":

SET udf_svp = Only({<vp_temp={"=vp_temp=round($1)"}>}saturated_vapour_pressures);

Henrik

Re: user defined function to select values from a table for subsequent calculation

rwunderlich — Sat, 10 Jan 2015 17:22:57 GMT

Change your chart type to a Straight Table and use an empty label. You should be able to see the substituted formulas in the headings. You can try it piece by piece.

-Rob

Re: user defined function to select values from a table for subsequent calculation

Sat, 10 Jan 2015 18:14:35 GMT

I tried ... this is the result ... can't figure out why it isn't working:

Re: user defined function to select values from a table for subsequent calculation

MarcoWedel — Sat, 10 Jan 2015 22:25:17 GMT

Hi,

instead of using a lookup table you could also calculate the saturated vapour pressure

e.g. as defined by the International Association for the Properties of Water and Steam (International Association for the Properties of Water and Steam😞

http://www.iapws.org/relguide/IF97-Rev.pdf

LET vN1  = 0.11670521452767e4;
LET vN2  = -0.72421316703206e6;
LET vN3  = -0.17073846940092e2;
LET vN4  = 0.12020824702470e5;
LET vN5  = -0.32325550322333e7;
LET vN6  = 0.14915108613530e2;
LET vN7  = -0.48232657361591e4;
LET vN8  = 0.40511340542057e6;
LET vN9  = -0.23855557567849;
LET vN10 = 0.65017534844798e3;
SET vϑ = ($1+273.15+$(vN9)/($1+273.15-$(vN10)));
SET vA = (pow($(vϑ($1)),2)+$(vN1)*$(vϑ($1))+$(vN2));
SET vB = ($(vN3)*pow($(vϑ($1)),2)+$(vN4)*$(vϑ($1))+$(vN5));
SET vC = ($(vN6)*pow($(vϑ($1)),2)+$(vN7)*$(vϑ($1))+$(vN8));
SET vp = pow(2*$(vC($1))/(-$(vB($1))+pow(pow($(vB($1)),2)-4*$(vA($1))*$(vC($1)),0.5)),4)*1e6;
SET VapPress = Dual(Num($(vp($1)),'0.0')&' Pa',$(vp($1)));

SET udf_water_content = Num(0.622*($1*$2)/(($3-$1*$2))*1000);

tabHumidity:
LOAD *,
    $(udf_water_content(rel.hum, sat_vap_pres, 101300)) as water_content;
LOAD *,
    $(vp(temp)) as sat_vap_pres;
LOAD Dual(RecNo()&' RH%', RecNo()/100) as rel.hum,
    Dual(IterNo()-1&' °C', IterNo()-1) as temp
AutoGenerate 100
While IterNo()<102;

tabSatVapPress:
LOAD temp,
    $(VapPress(temp)) as VapPress
FROM [http://community.qlik.com/thread/147952] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: user defined function to select values from a table for subsequent calculation

rwunderlich — Sat, 10 Jan 2015 22:50:52 GMT

Any chance you could upload a sample qvw?

-Rob

Re: user defined function to select values from a table for subsequent calculation

Thu, 15 Jan 2015 15:10:26 GMT

attached you can find a sample qvw with an excel sheet including some sample data and the saturated vapour pressures table. Thanks for reviewing!

Re: user defined function to select values from a table for subsequent calculation

Thu, 15 Jan 2015 15:12:18 GMT

Thanks a lot for the huge effort you invested to help me out, but I hope to find a much easier solution. Especially to transfer the approach to similar issues, because this is a very unike solution.

BR
Henrik

Re: user defined function to select values from a table for subsequent calculation

rwunderlich — Thu, 15 Jan 2015 23:10:07 GMT

I looked at your qvw and realized I took you down the wrong path with the Set expression. That is not going to work row by row. This will work as a chart expression though.

SET udf_svp = only(if(vp_temp = Round($1), saturated_vapour_pressures));

then

$(udf_svp(temperature))

I don't know if it fits your original requirement, but in your script you could add

.round(temperature) as vp_temp

to the data table which would create a linkage between temperature and saturated_vapour_pressures.

-Rob