Join, Link, Concat, ApplyMap, or all of the above? - Qlik Community

drewwilliams · ‎2016-01-18

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!

Gysbert_Wassenaar · ‎2016-01-18

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.

talk is cheap, supply exceeds demand

drewwilliams · ‎2016-01-18

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!

Report Inappropriate Content · ‎2016-01-18

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

drewwilliams · ‎2016-01-18

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

drewwilliams · ‎2016-01-18

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

Report Inappropriate Content · ‎2016-01-18

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?

drewwilliams · ‎2016-01-18

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?

drewwilliams · ‎2016-01-19

Anybody have any ideas?

Gysbert_Wassenaar · ‎2016-01-20

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

talk is cheap, supply exceeds demand