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.
You can also create your own field 'Revenue' as Type for the 1st type,, 'Cost' as Type for the 2nd one. So that you concatenate the tables into one single table, but can dissociate the revenues and the costs.
There are a number of approaches here, but a simplest may be to JOIN the shipments to the orders. I'm not clear why you would have a 'Revenue" field on both tables. If they both represent the same data, drop the Revenue field from the Shipments table. If they are different data, rename them to have different names.
If you want to select a single date and show both Orders and Shipments for that date, you can do that by creating a link table. Here's a tutorial on how to do that: