topic Re: Data modeling issue in QlikView

Data modeling issue

dseelam — Thu, 10 Nov 2016 15:06:40 GMT

Hello All,

Working on new project, to give a good start need some suggestion

I have 3 tables one Item master 2nd Customers 3rd Bom table, Now I can join Item Master & Customers using Item num and bring all the customer information and Finished goods info(as customer has only finished goods)

How to join Bom info if Item num are in 2 diff columns one for finished goods and one for components ???

Item Master
Item Num
FinishedGoods
Components
bbb

Customers
Item Num
Finished Goods
kkk
lllll

BOM
Item Num 1(for finished goods
Item Num2(components
Finished Goods
Components

I hope it make sense

Re: Data modeling issue

Anonymous — Thu, 10 Nov 2016 15:14:17 GMT

In the BOM table just write Autonumerhash128(Item Num 1(for finished goods,Item Num2(components) as Item Num

Re: Data modeling issue

sczetty — Thu, 10 Nov 2016 15:34:45 GMT

I would assume that the Item Master table has only finished goods. If that is the case then what you should do is to link the finished good Item Number to the BOM Item Number finished good field. That should give you the BOM components associated with the finished good.

If your BOM table has recursive elements (This is where an item can be both a component and a finished good.) then you have a much more complex problem.

Also, I cannot see how the information needs to be used in your application. For instance, do you just need the total of all the component costs, or do you need to have more complex data presentation that shows the hierarchy of the sub assemblies. If you just want totals or counts, it may be simple.

Steve

Re: Data modeling issue

Oleg_Troyansky — Thu, 10 Nov 2016 15:44:15 GMT

BOM applications are tricky... Since you need to deal with two types of Items - Products and Materials, you need to load your Item Master twice - once for Products and once for Materials, renaming all the corresponding fields accordingly...

Cheers,

Oleg Troyansky

Learn advanced Qlik techniques in my book QlikView Your Business

Re: Data modeling issue

aarkay29 — Thu, 10 Nov 2016 15:48:34 GMT

Hey Dinesh,

Load *

From [ITEM MASTER];

Load *

From BOM;

Left Join ( [ITEM MASTER])

ITEM NUM1,

FINISHED GOODS

RESIDENT BOM;

Left Join ( [ITEM MASTER])

ITEM NUM2,

COMPONENTS

RESIDENT BOM;

DROP TABLE BOM;

I am just assuming that this is what you want and I am unsure of the data.

If this does not solve your issue, Please provide a sample data.

Thanks

Re: Data modeling issue

dseelam — Fri, 11 Nov 2016 13:51:55 GMT

Stephen,

Item master has both Finished goods and components as mentioned in tables with single item number column