4 Replies Latest reply: Mar 27, 2012 11:10 AM by benjmoff RSS

    Calculation in the Load Script

      Hello,

       

      I load sales data by Transaction from one file,

       

      Transaction No.

      Product Name

      Barcode

      Product Category

      Qty

      Sales

      GM

      etc etc...

       

      But some products are measured by tonnage so I need to multiply the Qty by a field called Multiplier which I load from a different file.

       

      I currently use an expression to do this, Qty * Multiplier

       

      This works in my Straight Table when Barcode is the dimension

       

      But if I use category as the dimension the expression returns an error.

       

      so I am wondering if I can do a calculation in the load script to make a new field called Calculated Tonnage which would be Qty * Multiplier (Qty from the Sales Table, and Multiplier from the Multiplier Table).

       

      Any suggestions would be greatly appreciated,

       

      Thanks,

       

      Ben.

        • Calculation in the Load Script
          Wanderson Marques

          Hello Ben,

           

          If I understand what you want, you can do a left join between two tables and put the field multiplication as a new field in the sales table. Read this query again as Resident and create a new field by multiplying the desired field by field multiplication.

           

          Hope this helps!

          God Bless You!

           

          Stive

          Brasil

          • Calculation in the Load Script
            Jean-Pierre Bakhache

            Hi Ben,

             

            What you can do is a left join to the table containing the multiplier or a mapping load, in order to get the multiplier into your transactions table for the calculation.

            As i understood, the barcode is a key field so you can do like below:

             

             

            Example1:

             

            Map_Multiplier:

            //this table will be automatically dropped after script execution as the load is preceded by mapping

            Mapping Load Distinct Barcode, Multiplier

            From multiplier_table;

             

            Transactions:

            Load Barcode,

            Qty,

            Qty * ApplyMap('Map_Multiplier', Barcode) as CalculatedTonnage

            from transactions_table;

             

             

             

            Example2:

            tempTransactions:

            Load Barcode,

            Qty

            From transactions_table;

             

            left join

             

            Load Distinct Barcode,

            Multiplier

            From multiplier_table;

             

            Transactions:

            Load *,

            Qty * Multiplier as CalculatedTonnage

            Resident tempTransactions;

            drop table tempTransactions;

             

             

            Hope this helps you!