Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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');
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');
Check this..its working