topic Re: Link Table with different keys in QlikView

Link Table with different keys

Thu, 19 May 2016 18:59:54 GMT

Hi Experts,

I want to create a model using these 4 tables.

Item(Master)(Common Field Item No)

WE(Transaction Tables)(Common Field=> Item No.Location Code,Bin Code,Lot No.)

ILE(Transaction Tables)(Common Field=> Item No,Location Code,Bin Code, Lot No. )

VE(Transaction Tables)(Common Field=> Item No,Location Code)

I want to show show qty and cost group by Item No,Location Code,Bin code.

Challenge is Common fields are different in four tables and synthetic keys are occurring during Loading.

Please Advice,

Item:

LOAD [Item Category Code],

[Item No.],

Description,

[Product Group Code],

[Unit Cost]

FROM

(ooxml, embedded labels, table is ITEM);

WE:

LOAD

[Location Code],

[Bin Code],

[Item No.],

[Lot No.],

Quantity,

[Expiration Date]

FROM

(ooxml, embedded labels, table is WE);

ILE:

LOAD [Entry No.],

[Bin Code],

[Location Code],

[Item No.],

[Lot No.],

[Manufacturing Date]

FROM

(ooxml, embedded labels, table is ILE);

VE:

LOAD [Item No.],

[Cost Amount (Expected)],

[Cost Amount (Actual)],

[Location Code],

[Item Ledger Entry No.],

[Item Ledger Entry Quantity]

FROM

(ooxml, embedded labels, table is VE);

Thanks in advance,

Re: Link Table with different keys

Oleg_Troyansky — Thu, 19 May 2016 19:30:33 GMT

You can doing using a Link Table, but personally I'd prefer to concatenate all transaction tables into a single fact and restore missing associations with generic link tables.

I'm describing both approaches with a lot of detail in my book QlikView Your Business. Check it out if you'd like to learn advanced Data Modeling in QlikView.

cheers,

Oleg Troyansky

Re: Link Table with different keys

vvvvvvizard — Sun, 07 Jan 2018 15:18:09 GMT

which page in the book ? as i cant find a link table with different key combinations

Can someone do a link table with 2 ,3 , 4 key field combinations eg

product_id & customer_id

product_id & customer_id &supplier_id

product_id & customerid & supplier_id & debtor_id

so these are the common fields between the 3 tables

they each hav 20 other fields that are different in each table

Re: Link Table with different keys

RadovanOresky — Sun, 07 Jan 2018 16:23:19 GMT

Hi,

I agree with Oleg. Use Concatenate statement to load all transactional tables to one fact table. This approach is much better then link table, especially with large datasets.

FactTable:

LOAD ...<necessary fields>...

FROM

(ooxml, embedded labels, table is WE);

Concatenate (FactTable) LOAD ...<necessary fields>...

FROM

(ooxml, embedded labels, table is ILE);

Concatenate (FactTable) LOAD ...<necessary fields>...

FROM

(ooxml, embedded labels, table is VE);

Re: Link Table with different keys

Oleg_Troyansky — Thu, 11 Jan 2018 17:05:30 GMT

Responding to your question:

- Link Tables are described on pages 431-457

- Concatenated Facts are described on pages 628-647 in the book

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Prague this April!