7 Replies Latest reply: Apr 1, 2015 7:38 AM by fadi barri RSS

    Unit Conversion

      Dear Community Team,

       

      I have a problem when I want convert unit for the items.

      I have a table for unit conversion and is contains a following

      ItemID,FromUnit,ToUnit,Factor

      item001,Case,Unit,40

      item001,Case,Outer,20

      item001,MSU,Kilo,7

      item002,Case,Unit,15

      item004,Case,Unit,29


      and I have table for sales as example

      SalesID,ItemId,UnitID,Qty

      S001,item001,Unit,250

      S001,item002,Case,50


      I have a control contain all of units conversion and when I click to any unit I want must reflect

      all quantities in the charts.

        • Re: Unit Conversion
          Manish Kachhia

          How you want to Associate your data model ?

          UnitID of Sales Table equal to Which Unit of Item table... i.e. UnitID = FromUnit or UnitID = ToUnit?

           

          To create association, you need to create a common key..

          ItemiD & UnitID as Key1

          in Sales Table

           

          Same in Item Table

          • Re: Unit Conversion
            Manish Kachhia

            ItemTemp:

            Load * Inline

            [

              ItemID, FromUnit, ToUnit, Factor

              item001,Case, Unit, 40

              item001,Case, Outer, 20

              item001,MSU, Kilo, 7

              item002,Case, Unit, 15

              item004,Case, Unit, 29

            ];

             

             

            Item:

            Load ItemID, ItemID & FromUnit as Key1, FromUnit as UnitID, 'FromUnit' as Flag, Factor Resident ItemTemp;

            Load ItemID, ItemID & ToUnit as Key1, ToUnit as UnitID, 'ToUnit' as Flag, Factor Resident ItemTemp;

             

             

            Drop Table ItemTemp;

             

             

            Sales:

            Load SalesID, ItemId & UnitID as Key1, Qty Inline

            [

              SalesID,ItemId, UnitID,Qty

              S001, item001,Unit,250

              S001, item002,Case,50

            ];

             

            Now use below expression

             

            =SUM({<Flag = {'FromUnit'}>}Qty) + SUM({<Flag = {'ToUnit'}>}Qty*Factor)