4 Replies Latest reply: Apr 7, 2014 2:55 AM by Ravichandra Nadiminti RSS

    Script Variable from table.

    Tim Webber

      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

      [C:\Data\OPEN\tab26.xls]

      (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.
          NAGA CHIRANJEEVI THOTA

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

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

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

                Check this..its working

                1.png