Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Problem using a vba custom function in the load script

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...

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

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.

5 Replies

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

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.


talk is cheap, supply exceeds demand
Not applicable

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

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

Not applicable

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

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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.

Community Browser