Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Best way to combine data sets into one app?

Team,

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.

3 Replies
Not applicable

Re: Best way to combine data sets into one app?

Shelley,

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.

See page 17 of the fllowing doc.

Fabrice

neetha_p
Honored Contributor

Re: Best way to combine data sets into one app?

Best way to join two tables is using linking(Join) where tables are merged on common entity and records will not duplicate.

MVP & Luminary
MVP & Luminary

Re: Best way to combine data sets into one app?

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:

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

http://masterssummit.com