Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
First can you tell me ... UnitID = FromUnit or UnitID = ToUnit?
I use UnitId = FromUnit but if I can use UnitId = ToUnit may be that more than better.
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))
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)
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