Unit Conversion

[#1]

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.

[MK_QSL]

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

[#3]

Thanks Manish

Please can you give example if I want to create an expression in the chart for sum quantities.

and how can I benefit from the key1?

[MK_QSL]

First can you tell me ... UnitID = FromUnit or UnitID = ToUnit?

[#5]

I use UnitId = FromUnit but if I can use UnitId = ToUnit may be that more than better.

[#6]

I have an expression as

sum(if(FromUnit = GetFieldSelections(UC_TOUNIT),  QTYORDERED))

+

sum(if(ToUnit = GetFieldSelections(UC_TOUNIT),QTYORDERED*FACTOR,0))

Note: UC_TOUNIT is a list box controls to filtering as a parameter.

but this take a big time to resolve issues because the factor

in this line no problem is very fast:

sum(if(FromUnit = GetFieldSelections(UC_TOUNIT),  QTYORDERED))

but in this line is very long time, some times as 30 minutes,

sum(if(ToUnit = GetFieldSelections(UC_TOUNIT),QTYORDERED*FACTOR,0))

[MK_QSL]

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)

[#8]

Thanks Manish,

I want get a value for ToUnit if user click on unit conversion list box as parameter and it is name is UC_TOUNIT.

this controls to convert all of quantities according UC_TOUNIT