Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

exploding BOMs - join tables?

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,

1 Solution

Accepted Solutions
Employee
Employee

Re: exploding BOMs - join tables?

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;

11 Replies
Not applicable

Re: exploding BOMs - join tables?

I think that this is a better example

Table 1

parent_item_idpurchase_item_idqty.req
1A1
1B4
1C4

Table 2

   

item_iditem_descitem_qtyonhand
1TABLE15
ATOP1
BLEG3
CCHAIR2

Desired Table

   

parent_item_idparent_item_descpurchase_item_idpurchase_item_descqty.reqpurchase_item_qtyonhandparent_item_qtyonhand
1TABLEATOP1115
1TABLEBLEG4315
1TABLECCHAIR4215
Employee
Employee

Re: exploding BOMs - join tables?

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/Hier...

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;

Not applicable

Re: exploding BOMs - join tables?

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,

Employee
Employee

Re: exploding BOMs - join tables?

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;

Not applicable

Re: exploding BOMs - join tables?

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;

Employee
Employee

Re: exploding BOMs - join tables?

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

Not applicable

Re: exploding BOMs - join tables?

Here are my scripts...

Temp table according with your example

2015-12-09_10-47-52.png

BOM tree table.

bomitem_part_item_id and bomitem_item_id are = item_id

2015-12-09_10-49-22.png

Item master table - table with item descriptions

2015-12-09_10-50-05.png

Is this information enough for you?

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

Thanks,

Employee
Employee

Re: exploding BOMs - join tables?

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.

Not applicable

Re: exploding BOMs - join tables?

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

2015-12-14_12-01-45.png

Thanks for your help...