topic Re: Join, Link, Concat, ApplyMap, or all of the above? in QlikView

Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Mon, 18 Jan 2016 18:04:47 GMT

Hello All,

I've read articles and seen examples of joins, link tables, concatenates, applymaps and dimension tables, but I haven't found an example that matches my situation. From reading about each of these it seems I may need to do a combination of them in order to correctly link my data. I have many fact tables with either 2 or 3 key fields in them. These fact tables are very large (most have tens of millions of rows). Here is a simplified version of each table.

Key1, | Key1, | Key1, | Key1, | Key2, | Key1, | Key1,

Key2, | Metric11, | Metric13,| Key3, | Key3, | Key3, | Key2,

Table1 details the parts that make up a product, so these Keys are not distinct. The other tables hold details about the parts (Key2) and products (Key1), sometimes depending on their location (Key3).

Each of these tables have many more fields (Metrics) in them but this is a simplified version to show where the keys are in each table.

I've considered a link table but I'm not sure how to create the key field when each table has a different number of keys in them.

Can anyone help me connect this table correctly as to keep metrics from duplicating/summing incorrectly and achieve optimal performance? (keep in mind, some of these tables have millions of rows in them) With this much data, synthetic keys will give me incorrect results.

I appreciate any help! Thank you!

I've attached a sample with some made up data using inline tables, however my data will be coming from QVDs. I've also tried to decrease the space between tables in the explanation in hopes that it formats correctly when published.

EDIT 1/29/16 - STILL LOOKING FOR HELP!

Re: Join, Link, Concat, ApplyMap, or all of the above?

Gysbert_Wassenaar — Mon, 18 Jan 2016 18:25:35 GMT

That's pretty hard to read. Can you create a small qlikview document that illustrates the problem? Preferably with table and field names that say a little more about their purpose and content than TableX, KeyY and MetricZ. That will make it easier for us to make suggestions.

Re: Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Mon, 18 Jan 2016 18:31:13 GMT

Hi,

Yes sorry, I tried to shrink it once before but I guess that did not work. I'll create a QVW real quick and edit the post. Thanks!

Re: Join, Link, Concat, ApplyMap, or all of the above?

Mon, 18 Jan 2016 18:33:27 GMT

Can you please post a sample qvw with sample data so community will give best possible solution.

Re: Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Mon, 18 Jan 2016 19:36:03 GMT

Sample data has been posted, some qty/ values may not make sense but the outline of the data should detail my current situation.

Re: Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Mon, 18 Jan 2016 19:36:09 GMT

Sample data has been posted, some qty/ values may not make sense but the outline of the data should detail my current situation.

Re: Join, Link, Concat, ApplyMap, or all of the above?

Mon, 18 Jan 2016 20:16:39 GMT

Can you explain how Inventory & BOM tables are related. You want to separate the Parts & products in differently ?

currently you have only Prod sales. Do you have separate sales table for Parts?

Re: Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Mon, 18 Jan 2016 21:44:15 GMT

Sure! There are no sales for parts. For inventory, it depends on the location. At a location, there will be both Parts and Products separately with inventory.

For example, let's say a Pencil is made of a wood sleeve, metal eraser cap, rubber eraser, and a graphite stick.

In inventory there could be, 10 Pencils, 4 wood sleeves, 2 metal eraser caps, 8 rubber erasers, and 7 graphite sticks. So if you were going to make more pencils with the part inventory, you could make 2 more pencils since there are only 2 metal eraser caps. After making 2 more pencils, the inventory would look like:

Pencils 12

Metal eraser caps 0

Wood Sleeves 2

Rubber eraser 6

Graphite Sticks 6

While the BOM file would list the parts in 1 product.

Prod Part Qty Parts in Prod

Pencil Metal eraser cap 1

Pencil Wood sleeve 1

Pencil Rubber Eraser 1

Pencil Graphite Stick 1

Does this make sense?

Re: Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Tue, 19 Jan 2016 20:56:06 GMT

Anybody have any ideas?

Re: Join, Link, Concat, ApplyMap, or all of the above?

Gysbert_Wassenaar — Wed, 20 Jan 2016 18:12:47 GMT

Perhaps you can concatenate the Inventory and BOM tables. See attached example.

Re: Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Wed, 27 Jan 2016 19:30:42 GMT

Sorry for the slow reply. I appreciate you taking the time to help me solve this, however, I'm still running into an issue when looking at your example.

If I want to view the inventory of the parts that make up a selected product, how would I create that connection?

In your example, I created a bar chart with the dimension Part. The expression is Avg(Inv_Qty). When selecting a Product from a listbox, I want the bar chart to display the Part numbers that make up the selected Product and the Part's Inventory Qty (with respect to a selected Inv_Date if one is chosen).

Right now your example displays this:

It does not list out the Parts that are in the selected Product. I think this may be due to how the Inventory and BOM tables were linked.

Thoughts?

Re: Join, Link, Concat, ApplyMap, or all of the above?

drewwilliams — Fri, 29 Jan 2016 05:17:23 GMT

STILL LOOKING FOR HELP!

Does anyone have any more input? I really need help connecting my data model.

To make it simpler I've made an Excel doc with a new example set of data. I need to connect the following sheets within the attached Excel file.

I need to be able to select a Product and in one table, see the corresponding Parts that make up the selected Products while in another chart, I want to see the inventory quantities for the Parts that are in the selected Products. Concatenating the inventory and BOM tabs does not work because then the Part inventory is not linked when a Product is selected.

This is very important and in need asap! Thank you all for your help!