6 Replies Latest reply: Jun 9, 2011 10:18 AM by Rogerio Faria RSS

    Expression on load script

      Hi all,

       

      I think my problem is simple, but I couldn't solve it.

      On my load script, I have a memory table "OP_Cost" like this:

      OP_Cost:

      Load

           yearOP,

           monthOP,

           Dept,

           Cost; // from ....

       

      Ok, now I have the table "Performance" that is not associated with "OP_Cost":

      Performance:

      Load

           yearP,

           monthP,

           Sum( {$<Dept={'Finance'}, yearOP={yearP}, monthOP={monthP}>} OP_Cost.Cost),

           Target; // from ....

       

       

      My doubt is how can I write the expression (in red), so I can get a sum for field "Cost", filtering the Dept, yearOP and monthOP.

      Not necessary must be an expression. If not, what else can I do ??

       

      Thanks all,

       

      Rogério Faria

        • Re: Expression on load script
          Erich Shiino

          Hi, Rogério. I don't think it is possible to do something like this.

           

          You will have to create a temporary table and use left joint to get apply the values you want.

          I think that, in your case, you would have something like this:

          //AFTER OP_COST

          Performance:

          Load yearP, monthP, Target

          from ...

           

          tempPerformance:

          Load yearOp as YearP, monthOP as monthP,  sum(Cost)

          resident OP_Cost

          where Dept = 'Finance'

          group by yearOp, monthOp;

           

          left join (Performance)

          load * resident tempPerformance;

          drop table tempPerformance;

           

          You probably need to correct something in my code before using it... 

          It's just to give an idea!

          Hope it helps

           

          Erich

           

          EDIT: Sorry... just corrected the name!

          • Expression on load script
            susant Kumar swain

            Try this

             

             

            Performance:

            Load

                 yearP,

                 monthP,

                 Target;

             

             

             

             

            OP_Cost:

            load

             

            yearop,monthop,,sum(cost)

             

            from  ..

            where exists(yearP,yearop)

            and exists(monthP,monthop)

            and Dept='Finanace'

            group by yearop,monthop;

             

            you can rename this table to someother name i just did this in the original table

            • Re: Expression on load script

              Well, thanks for all the replies. All of them helped me somehow.

              But, after some tests, if decided to do it like this:

               

              OP_Cost:

              Load

                   yearOP,

                   monthOP,

                   Dept,

                   Cost,

                   0 as Target; // from ....

               

               

              Concatenate(OP_Cost)

              Load

                   yearP as YearOP,

                   monthP as monthOP,

                   Dept,

                   0 as Cost,

                   Target; // from ....

               

               

              Like this, I only insert rows in the table, and the loading process finishes quickly.