Multiple sources, at least two different associations needed, preventing associations in some cases, skipping Nulls

[Learnin]

I am brand new to QlikSense ~ a couple of weeks of exploring what can be done. I have a bit of programming experience in other languages and understand datastructures and SQL well enough. But I am struggling to find and understand the capabilities, syntax and techniques within QlikSense App.  I would greatly appreciate any suggestion on how to have related  but independently functioning associations with many to many relations, and excluding Null values in the example PartDef source but not in the  InvBinQty source.

I need to create a dashboard in which the user can search 'Source1 (a loaded section) for a finished goods part number (column label %Part).

Once selected, other panels in the sheet need to express related data to this selected part number from other loaded sources (sections, with each of those sources also containing %Part). I am able to do that easily for some of the other sources with direct relationships, but one technique is alluding me.

In one source, the Primary Part number %Part is the primary key with many attributes in the other columns. Some of those columns contain the primary parts' material component part numbers. Both the primary and material part numbers can be found in the inventory file. Rather than looking for inventory for the Primary part number, I need to find inventory for each of the material part numbers (which may be in multiple bins) and show an aggregate sum from those bin qty amounts for each material part number and show those in a panel on the sheet. I do not want to search for or return the Primary key part number inventory Qty because it would always be zero since we make to order.

I think what would work is to create a temp table with one column or a variable that contains the list of material Part numbers and create an association to them and the Inventory Bin file. I cannot seem to find out how to create a subordinate table, list or 'View' and use that to do the inventory lookup on the materials of the Key Part, rather than the Key Part itself. One added wrinkle is that some columns are blank within the columns for material part numbers in the row of the primary part. I've attached examples to illustrate the two confounding source files:

PartDef - export of FG Part and its materials parts

InvBinQty - export of inventory with part, Bin and qty where both FG Part and Materials Parts are included

[AustinSpivey]

Welcome to Qlik!

So you want to be able to sum up the bin quantities ([Qty]) grouped on material number ([material1], [material2], etc.) instead of being grouped on part number ([%Part]), correct?

What I would do is unpivot the [PartDef] table such that the [materialn] fields transform into just two fields: an attributes field ("material1", "material2") and then a values field ("7410D22", "4567-34"). That way you can make selections on the material numbers and easily group your [Qty] aggregation.

Since you're new to Qlik, I'll show you how to do this using the Data Manager, which is the drag-and-drop, GUI-first ETL tool that you can use to load and transform your app's data.

The following instructions assume that you already have an app with data in it that matches the data you provided as an example.

1. Open your app and, at the top-left part of the page, select the Prepare tab and then select the Data Manager option:
   
2. From there, open the [PartDef] table by selecting it and then clicking the "pencil" icon at the bottom. Then select the Unpivot button at the top of the screen, click each of the [materialn] fields (make sure the top of each column turns orange). Finally, select the Apply unpivoting button below that table area:
   
   
   
3. Optionally, rename the two resulting fields to better fit the data:
   
   
   
4. When done, select the X button at the top-right part of the screen:
   
   
   
5. Make sure that the two tables are properly associated on the [%Part] field. Qlik should already recognize that as a common field and should display an "association suggestion" -- go ahead and apply that suggested link:
   
   
   
6. Now click on the Sheet tab at the very top of the page to get to the dashboard view:
   
   
   
7. Add our new [Material ID] field as a dimension and then add the [Qty] field as a measure (make sure the aggregation function it uses is Sum. Then, make it a table:
   

Hopefully all of that made sense and addressed your problem. If it didn't replay and let me know, I'd be happy to help more!