topic BOM linking issue - two dates in QlikView

BOM linking issue - two dates

captain89 — Tue, 05 Jun 2018 15:15:50 GMT

Hi,

i need to link the date of two fact tables: invoices and purchases.

I work for a group of industrial companies. One of these is a contractor so it buy the components from a company (of the same group) and it sell the product to the same company after a manufacture process.

I have a complex data model but the problem comes from these 3 tables: Bills of materials, Invoices and Purchases

BOMs:

product_code,

component,

quantity

....

Invoice:

invoice_date,

product,

invoice_qty,

invoice_price

Purchase:

purchase_date,

component,

purchase_qty,

purchase_price

I try to create a master calendar to link purchases and invoces like this:

Master Calendar:

Date,

invoice_date,

purchase_date

but there is a circolar problem.. maybe i must create a bridge table? How?

I need to see the difference between cost and prices... and want to select different linked periods

thanks for help

Re: BOM linking issue - two dates

YoussefBelloum — Tue, 05 Jun 2018 15:27:37 GMT

Hi,

try this:

1. on every table (Invoice, Purchase), rename every specific date field to Date, like this:

Invoice:

LOAD

invoice_date as Date,

product,

invoice_qty,

invoice_price

Purchase:

LOAD

purchase_date as Date,

component,

purchase_qty,

purchase_price

2. Concatenate these two tables, like this

Invoice:

LOAD

Date,

product,

invoice_qty,

invoice_price

Concatenante(Invoice)

Purchase:

LOAD

Date,

component,

purchase_qty,

purchase_price

Like this, you will have one physical table connected with one field to the Calendar table.

but depending on the rest of the model, this solution may not be the right one for you

Re: BOM linking issue - two dates

captain89 — Tue, 05 Jun 2018 15:42:06 GMT

Hi Youssef. Thanks for your answer.

I think of this solution too.. but in this case I create a single fact table with:

Date,

product,

component

invoce_qty,

invoice_price,

purchase_qty,

purchase_price

But the join with BOMs...?? product and component will create a synt key i think.

I'm creating 3 calendars and handle the problem with expression and set analysis... i'm looking for a better solution

Re: BOM linking issue - two dates

YoussefBelloum — Tue, 05 Jun 2018 16:03:06 GMT

I see, will try to create sample data and get back to you

Re: BOM linking issue - two dates

YoussefBelloum — Wed, 06 Jun 2018 11:54:44 GMT

Hi,

would you be able to take a screen shot of your Qlik datamodel?

I need a brief description on how the component and products are linked on the BOM table, is it like this:

Product1, Component1, qty,...

Product1, Component2, qty,...

Product1, Component3, qty,...

the quantity field of the Bom table represents what type of quantity? is it the Purchase Qty ? the Invoice Qty ? or another calculated qty ?

Re: BOM linking issue - two dates

YoussefBelloum — Wed, 06 Jun 2018 13:17:07 GMT

I think you can create a Bridge table to link BOM to your fact or maybe concatenate it, it will be interesting if you can make an extraction of some lines from BOM, INVOICE, PURCHASE for one or two product for example, or create Sample data

Re: BOM linking issue - two dates

captain89 — Fri, 15 Jun 2018 11:27:52 GMT

Hi,

in the Bom table there is the quantity of the component needed to make the product code.