2 Replies Latest reply: Feb 6, 2016 10:24 AM by Sunny Talwar RSS

    Calculated column in line table based on master table column

    Mohammad Adnan Ansari

      I have sales master and sales detail table. Both are available in qvd format.

       

      Sales Master has invoice no, customer no, discount % and other columns.

       

      Sales detail has invoice no, line no, item no, quantity and sales value and other columns.

       

      I would like to add a discount value column in sales detail table where the calculation formula is: discount value = sales value * (discount % /100).

       

      how this is achieved in the script when I load the data.

       

      Within my data model I have created only one sales table where I first load the sales master and then left join it with the sales detail table based on invoice no field.

        • Re: Calculated column in line table based on master table column
          Sunny Talwar

          May be something like this:

           

          MappingTable:

          Mapping

          LOAD [invoice no],

                    [discount %]

          FROM [Sales Master];

           

          [Sales Detail]:

          LOAD [invoice no],

                    [line no],

                    [item no],

                    quantity,

                    [sales value],

                    [sales value] * ApplyMap('MappingTable', [invoice no]) as [discount value]

          FROM [Sales Detail];

            • Re: Calculated column in line table based on master table column
              Sunny Talwar

              And if you are already left joining them, you can disregard the above suggestion and simply perform your calculation in a resident load of joined tables.

               

              Sales Master has invoice no, customer no, discount % and other columns.

               

              Sales detail has invoice no, line no, item no, quantity and sales value and other columns.

               

              [Sales Master]:

              LOAD [invoice no],

                        [discount %],

                        [other columns]

              FROM [Sales Master];


              Left Join ([Sales Master])

              LOAD [invoice no],

                        [line no],

                        [item no],

                        quantity,

                        [sales value]

              FROM [Sales Detail];


              FinalTable:

              LOAD *,

                        [sales value] * [discount %]/ as [discount value]

              Resident [Sales Master];


              DROP Table [Sales Master];