Script Variable from table.

[Anonymous]

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;

[chiru_thota · Accepted solution]

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;

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Check this..its working