topic exploding BOMs - join tables? in App Development

exploding BOMs - join tables?

Tue, 08 Dec 2015 19:47:40 GMT

Hi guys,

I just started working with Qlik Sense and I'm trying to find out how I can do with the following data:

Table 1:

parent_item_id

purchase_item_id

Table 2:

item_id

item_description

I need a table with description and all other information (table 2) for both fields on my table 1.

Is the best option a Link Table? How can I do it in this case?

Desired table:

parent_item_id

parent_item_id_description

purchase_item_id

purchase_item_id_description

All ways that I tried resulted in a synthetic key and it can't get the info that I want.

Thanks,

Re: exploding BOMs - join tables?

Tue, 08 Dec 2015 19:56:23 GMT

I think that this is a better example

Table 1

parent_item_id	purchase_item_id	qty.req
1	A	1
1	B	4
1	C	4

Table 2

item_id	item_desc	item_qtyonhand
1	TABLE	15
A	TOP	1
B	LEG	3
C	CHAIR	2

Desired Table

parent_item_id	parent_item_desc	purchase_item_id	purchase_item_desc	qty.req	purchase_item_qtyonhand	parent_item_qtyonhand
1	TABLE	A	TOP	1	1	15
1	TABLE	B	LEG	4	3	15
1	TABLE	C	CHAIR	4	2	15

Re: exploding BOMs - join tables?

JonnyPoole — Tue, 08 Dec 2015 20:09:13 GMT

Is table1 a 'node pair table' where purchase item IDs themselves may also be Parent IDs.

If yes you may need to use the HIERARCHY load feature to turn it into a tree of values

http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/ScriptPrefixes/Hierarchy.htm?Highlight=…

If purchase items themselves are never parents, my question is whether purchase items are the same IDs as item ids ?

if yes , just alias Item ID.

Table 1:

load

parent_item_id

purchase_item_id

from <>

Table 2:

load

item_id as parent_item_id

item_description as parent_item_description

from <>

Table 3:

load

item_id as purchase_item_id

item_description as purchase_item_description;

Re: exploding BOMs - join tables?

Tue, 08 Dec 2015 21:08:37 GMT

Thanks,

So for my case I need to use Hierarchy. However, I don't have NodeDescription in my table. As you can see my Nodedescription also on the same table as my ParentDescription.

Thank you,

Re: exploding BOMs - join tables?

JonnyPoole — Tue, 08 Dec 2015 21:26:40 GMT

Ok... so lets join the table together first and then we can do a hierarchyload

//join the tables to item and parent and descriptions are all joined together in a temp table

Temp:

load

parent_item_id

purchase_item_id

from <>

inner join (Temp)

load

item_id as ppurchase_item_id

item_description as parent_item_description

from <>

inner join (Temp)

load

item_id as purchase_item_id

item_description as purchase_item_description;

//read the combined table as a hierarchy

Data:

Hierarchy(purchase_item_id, parent_item_id, purchase_item_description,parent_item_description)

LOAD

purchase_item_id,

parent_item_id,

purchase_item_description,

parent_item_description

resident Temp;

//get rid of the temp table

drop table Temp;

Re: exploding BOMs - join tables?

Wed, 09 Dec 2015 16:21:26 GMT

Hi Jonathan,

Thanks for your help. Would you mind in help me with one more little thing?

I'd create these table however I have problem to connect my table and it shows the problem below. Do I need to put the full path (directory and my data base file name)?

Thanks

The following error occurred:

Invalid Path

---

e error occurred here:

Temp:

load

bomitem_parent_item_id,

bomitem_item_id

From bomitem;

Re: exploding BOMs - join tables?

JonnyPoole — Wed, 09 Dec 2015 18:47:45 GMT

I'm not sure, can you send me your script or a screenshot ? i have a feeling the From line is not correct.

Re: exploding BOMs - join tables?

Wed, 09 Dec 2015 18:59:43 GMT

Here are my scripts...

Temp table according with your example

BOM tree table.

bomitem_part_item_id and bomitem_item_id are = item_id

Item master table - table with item descriptions

Is this information enough for you?

I'm using Qlik Sense Server version with Qlik ODBC Connector Package to access my data.

Thanks,

Re: exploding BOMs - join tables?

JonnyPoole — Wed, 09 Dec 2015 21:25:20 GMT

The name of the table [Tablename]: should only occur immediately before the LOAD statement. In your 1st screenshot it is incorrectly located before an SELECT statement. In the 2nd screenshot you have an extra one before the LIB CONNECT statement and an extra one before the SELECT statement.

Make those 3 changes and see if things change or are fixed.

Re: exploding BOMs - join tables?

Mon, 14 Dec 2015 17:07:28 GMT

Hi Jonathan,

Sorry for my delay, I took couple days off. So, now loading my data is working I had added Load...Select... as you can see in my script. However, the hierarchy is not working because it says:

I'm trying fix it but still not working. Do you have any suggestion?

The following error occurred:

Field names must be unique within table

Thanks for your help...

Re: exploding BOMs - join tables?

JonnyPoole — Mon, 14 Dec 2015 17:21:01 GMT

Try this... (remove the 4th field from the load, but leave it between the () after the hierarchy word:

Data:

Hierarchy(bomitem_item_id,bomitem_parent_item_id,bomitem_item_descrip,[bomitem_parent_item_description])

load

bomitem_item_id,bomitem_parent_item_id,bomitem_item_descrip

resident Temp;

Re: exploding BOMs - join tables?

Mon, 14 Dec 2015 18:07:27 GMT

Now it's working...thanks Jonathan.