topic Re: Problem using a vba custom function in the load script in QlikView

Problem using a vba custom function in the load script

Wed, 04 Sep 2013 12:22:26 GMT

Dear all,

I am trying to load weather observation data but I need to an additional parameters (the wet bulb temperature) to this table. Because it is not a straight-forward calculation, rather an iteration, I need to implement a custom function defined in a vba script. The problem is that other fields in the loaded table are influenced by this vba calculation. For example, where before the load I only have 30 unique records in the field LOCATION, after the load I have 682. I have the impression that the extra records are somehow a mix of other field records. For example: "cloudy-1.03" is one new record. Another: Showers07.

This is the load script part:

WEATHER_OBSERVATIONS_TEMP:

LOAD *,

LOCATION,

WetBulbTemp(tempm,hum,pressurem) as TEMPERATURE_WET_BULB

RESIDENT WEATHER_OBSERVATIONS_TEMP2;

This is the VBA code:


Public Function WetBulbTemp(Ta,U,Ps)
    ' Ta = ambient (dry bulb) air tempeature (°C)
    ' U = relative humidity (%)
    ' Ps = air pressure (mBar or HPa)
    
    ' Tw = intermediate result for wet bulb temperature
    
    ' formula from http://www.knmi.nl/samenw/geoss/avw/RIS-doc.pdf
    
    ' initialise wet bulb temperature and vapor pressure
    T = Ta
    E = VaporPressure(Ta)*U
    F = 1
    
    do while abs(F) >= 0.0001
        F = (VaporPressure(T)-E)/(5419*VaporPressure(T)/(273.15+T)^2+0.000646*Ps)
        T = T - F
        E = E+0.000646*Ps*F
    loop

    WetBulbTemp = T
End Function

Public Function VaporPressure(T)
    VaporPressure = 6.11213*exp(17.5043*T/(241.2+T))
End function

Can anyone explain what is going on here? I already tried changing the vba script to:

Public Function WetBulbTemp(Ta,U,Ps)
    WetBulbTemp = 1
End Function

But the problem remains...

Re: Problem using a vba custom function in the load script

Gysbert_Wassenaar — Wed, 04 Sep 2013 12:57:05 GMT

I'd make sure those 'new' locations don't exist in WEATHER_OBSERVATIONS_TEMP2. That's where they seem to be coming from. I don't believe in QV randomly generating values like Showers07 from nowhere.

Re: Problem using a vba custom function in the load script

Wed, 04 Sep 2013 13:32:35 GMT

When I comment out the line with the VBA function then the script loads correctly and I have no issue with extra records...

Re: Re: Problem using a vba custom function in the load script

Wed, 04 Sep 2013 13:46:19 GMT

Ok I have removed as much as possible in order to zoom in to the problem. The script is the following:

WEATHER_OBSERVATIONS_TEMP2:
LOAD LOCATION as LOCATION_ORIGINAL
FROM
[WEATHER_OBSERVATIONS.QVD](qvd);

NoConcatenate

WEATHER_OBSERVATIONS_TEMP:
LOAD WetBulbTemp(1) as TEMPERATURE_WET_BULB,
     LOCATION_ORIGINAL as LOCATION_NEW
RESIDENT WEATHER_OBSERVATIONS_TEMP2;

The VBA script is:

Public Function WetBulbTemp(Ta)
    WetBulbTemp = 1
End Function

LOCATION_ORIGINAL contains 27 records

LOCATION_NEW contains76 records

The orginal QVD contains 553.955 records. I have tried this on 2 computers and both give me the same problem. The number of records in LOCATION_NEW fluctuates with each reload...

Re: Problem using a vba custom function in the load script

Gysbert_Wassenaar — Wed, 04 Sep 2013 13:49:59 GMT

Can you post that qvd file? Or create one (with sensitive data scrambled to make it unreadable) with enough data to reproduce the issue?

Re: Problem using a vba custom function in the load script

Wed, 04 Sep 2013 21:12:33 GMT

I seem to have found a workarround. Instead of loading the QVD first and then doing all the transformations in a resident load, I've now added an extra step. The VBA script runs in a seperate resident load. So in summary:

1) LOAD * FROM QVD

2) RESIDENT LOAD + transformations without VBA_FUNCTION

3) RESIDENT LOAD *, VBA_FUNCTION

This seems to work fine. I believe this is a bug in QlikView somewhere.