3 Replies Latest reply: Feb 6, 2014 3:31 PM by Rob Wunderlich RSS

    Best way to combine data sets into one app?



      I am wondering the best way to combine orders and shipments into one file, so each may be evaluated in the same app.  This is challenging because there are so many of the same types of information in both tables: order#, customer#, project name, etc.  I am using QVD Files that I would like to combine in a Model.  The orders table has "create date" (when the order was entered) and shipments has "ship date" (when the order shipped).  The orders table contains all orders whether they have shipped or not, while the shipments table only contains orders that have shipped.


      How can I also create an app that shows both shipments and incoming data in one app, with the user selecting one date, which then selects the ship date and the created date = to the selected date?


      I could use CONCATENATE, but both tables have a field called "Revenue," so concatenating seems like it would result in double-counting dollars whenever I sum on "Revenue."  Maybe the "Revenue" field should be named something different in each file?  For example, Order $ in incoming orders, and Shipped $ in shipments?


      Maybe I should JOIN the shipments onto each corresponding order?


      I'm not expecting specific commands here. If somebody can point me in the right direction on ideas, I will research on my own.