Forum Discussion

mdp1202's avatar
mdp1202
Frequent Visitor
6 years ago

Need help calculating and converting UOM between two tables

I have one data source that provides costs in EA , CA, PL, or LB. I have a second data source that reports issues, sometimes quantities are reported by Each, Cases, Pallets, or Pounds.  So my first...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi mdp1202 ,

Can you please share some sample data and expect the result to help us clarify your requirement? It is hard to test without any detail information.

How to Get Your Question Answered Quickly

>>Then, I am not sure how to cross-reference these sources. If data source 2 reports 50 cases today, it needs to pull the cost from data source 1, which may not match UOM (it may be EA).

Maybe you can try to create a dimension table with unique id field to link these datasources. If you mean calculate from all datasoruces, I'd like to suggest you merge these datasoruce tables on 'query editor' side.

Regards,

Xiaoxin Sheng

mdp1202's avatar
mdp1202
Frequent Visitor
6 years ago

Attached is some sample data - the issues are pulling from one data source thru the 'Quantity' & 'UOM' columns. There is a link between data sources using 'Reference_Number' column. Data source two has 'Standard Cost' and 'UO' fields.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi mdp1202 ,

    According your snapshot, it seems like your datasources contain different format UOM values, I'd like to suggest you add a mapping table to map these different UOM values. (similar as dictionary table)
    Then you can use this mapping table as bridge to link all datasources UOM fields.

    Regards,

    Xiaoxin Sheng