2 Replies Latest reply: Aug 24, 2012 11:32 AM by Sander Janssen RSS

    LOAD, dynamic, variable to table

      Hi!

      Trying to simulate a montly report. To do that I need to save aggregated data in a table. No problem saving vVar, but I want to save the calculated value - in this case 830.

       

      LET vVar='sum({<region={1}>}sales)';

       

      sales:
      LOAD * Inline [
      region,sales
      1,200
      1,630
      2,300];

       

      x:
      LOAD $(vVar) AS Value AUTOGENERATE(1);

       

      Any suggestions?

        • Re: LOAD, dynamic, variable to table
          Richard Pearce

          Hi Thomas,

           

          You can't use set analysis in a script, you could generate aggr tables and peek the results; something like this:

           

           

          sales:
          LOAD * Inline [
          region,sales
          1,200
          1,630
          2,300];


          Sales_Sum:
          NoConcatenate LOAD
          sum(sales) as sum_sales
          Resident sales_temp
          where region=1
          group by region;


          LET vVar=Peek('sum_sales',0,'Sales_Sum');


          x:
          LOAD $(vVar) AS Value AUTOGENERATE(1);

           

           

          You could use varibles / loop / curser tables to define the field you're checking and the field you're grouping by (set) if your requirement is more complex than your example.....

           

          Hope this help....

            • Re: LOAD, dynamic, variable to table
              Sander Janssen

              Hi, another way to do it. Less suitable if you have a lot of regions, too much (nested) if's, but it does the job here. Less SQL, more formulas. Also gives

              you the possibilty to do any calc on the sales figures on scriptlevel and then have it available as flat records in the new inline table. Depends on your plans. Might help...


              Let c=0;
              Let d=0;

               

              sales:
              LOAD * Inline [
              region,sales
              1,200
              1,630
              2,300];

               

              For i=0 to NoOfRows('sales')-1;

                    Let a=Peek('region', $(i), 'sales');
                    Let b=Peek('sales', $(i), 'sales');

               

                   If $(a)=1 then
                   Let c=$(c)+$(b);
                   Endif

                   If $(a)=2 then
                   Let d=$(d)+$(b);
                   Endif

               

              Next i;

               

              x:
              LOAD * Inline [
              Reg1_Tot, Reg2_Tot
              $(c), $(d)
              ];

               

              Regards.