topic Re: Script Variable from table. in QlikView

Script Variable from table.

Anonymous — Sun, 06 Apr 2014 23:34:44 GMT

Here's is my script. My issue is that the two variable are not getting evaluated or rather the result of the expressions ( let AVG_DRATE and let STD_DRATE) are NULL values. They should be the average and std deviation respectively from one column in my table INFANT_MORTALITY_T. The values should then be used by my subsequent load statement to get the zscore (normdist) function. All works fine when i hard code the variables to numeric values. What am I doing wrong?

INFANT_MORTALITY_T:

LOAD

[Local Health Area] as LHA,

2002 as CAL_YEAR_NUM,

Deaths,

Ratio,

Rate

FROM

(biff, embedded labels, header is 3 lines, table is TAB26$)

where len([Local Health Area])<=3;

let AVG_DRATE = avg(INFANT_MORTALITY_T.Rate);

let STD_DRATE = Stdev(INFANT_MORTALITY_T.Rate);

//let AVG_DRATE = 5;

//let STD_DRATE = 4;

INFANT_MORTALITY_F:

LOAD

LHA as LHA,

CAL_YEAR_NUM as CAL_YEAR_NUM,

INFANT_MORTALITY_T.Deaths as Deaths,

INFANT_MORTALITY_T.Ratio as Ratio,

INFANT_MORTALITY_T.Rate as Rate

,NORMDIST(INFANT_MORTALITY_T.Rate,$(AVG_DRATE),$(STD_DRATE)) AS Zscore

Resident INFANT_MORTALITY_T;

QUALIFY Deaths, Ratio, Rate;

DROP Table INFANT_MORTALITY_T;

Re: Script Variable from table.

chiru_thota — Mon, 07 Apr 2014 00:42:51 GMT

Can you try as below ?.It worked for me.

table1:

LOAD * INLINE [

CountryName,State,Sales,Plan

America,NC,300,700

America,TX,800,900

India,AP,400,500

India,TN,400,500];

table2:

load avg(Sales)as Avg_Sales,Stdev(Sales) as Stdev_Sales resident table1;

let AVG_DRATE=peek('Avg_Sales',0,'table2');

let STD_DRATE=peek('Stdev_Sales',0,'table2');

drop table table2;

Re: Script Variable from table.

Anonymous — Mon, 07 Apr 2014 01:31:34 GMT

Well its closer! My temporary table has the two columns and correct values in one row but the variables are still empty/null. Very strange. This seems like it should work...

IFTEMP:

load

avg(INFANT_MORTALITY_T.Rate) as AVG_DRATE,

Stdev(INFANT_MORTALITY_T.Rate) as STD_DRATE

Resident INFANT_MORTALITY_T ;

let AVG_DRATE = PEEK('AVG_DRATE',0,'IFTEMP');

let STD_DRATE = PEEK('STD_DRATE',0,'IFTEMP');

Re: Script Variable from table.

Anonymous — Mon, 07 Apr 2014 01:39:35 GMT

Ahh, so i needed to "qualify" my field name. This is reasonable on the one hand but seems very redundant because i am specifying the table name explicitly in the function. Thx for your help!

IFTEMP:

load

avg(INFANT_MORTALITY_T.Rate) as AVG_DRATE,

Stdev(INFANT_MORTALITY_T.Rate) as STD_DRATE

Resident INFANT_MORTALITY_T ;

let AVG_DRATE = PEEK('IFTEMP.AVG_DRATE',-1,'IFTEMP');

let STD_DRATE = PEEK('IFTEMP.STD_DRATE',-1,'IFTEMP');

Re: Script Variable from table.

Anonymous — Mon, 07 Apr 2014 06:55:02 GMT

Check this..its working