Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
twebber
Contributor II

Script Variable from table.

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;

1 Solution

Accepted Solutions
chiru_thota
Valued Contributor

Re: Script Variable from table.

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;

4 Replies
chiru_thota
Valued Contributor

Re: Script Variable from table.

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;

twebber
Contributor II

Re: Script Variable from table.

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');

twebber
Contributor II

Re: Script Variable from table.

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');

ravic906
Contributor III

Re: Script Variable from table.

Check this..its working

1.png

Community Browser