topic Lookup tables in QlikView

Lookup tables

rubinsky — Wed, 28 Dec 2011 22:37:45 GMT

I have a series of tables. I have a table of Invoices, a table of Purchase Orders, and a table of Items.

Both the Invoices table and the Purchase Orders table have a field called PurchaseOrderID so they share a key. They also both have a field called ItemID, which I do not want them to share. My Items table also has a field called ItemID that I want shared with the other two tables.

I want my Purchase Orders and Invoices tables to share PurchaseOrderID together but not ItemID

I want my Purchase Orders and Invoices tables to share their ItemID field with the Items table but not each other.

I'm going to have other tables that meet this scenario, so what is the best approach to this?

-jdr

Lookup tables

johnw — Thu, 29 Dec 2011 00:23:03 GMT

If the invoice is always for the same item as the purchase order, just remove the item field from the invoice. I don't think that's your situation, though. I gather that you might invoice a different item than the purchase order specifies? Like you might ship them a different item as a substitute?

You could duplicate the item table with different field names for everything (in this case, something like "Purchase Order Item" and "Invoice Item" as prefixes). This is very common in QlikView.

Another option that normally applies is to create a linkage table linking the purchase order, invoice and item tables. However, since the purchase order and invoice also connect, that would cause a loop, which is not allowed. There's probably a way around this, but it's not jumping out at me.

Lookup tables

SunilChauhan — Thu, 29 Dec 2011 05:46:10 GMT

order:

Load *

from order;

Join

Load *

PurchaseOrderID as item_PurchaseOrderID

from Item;

to lookup PurchaseOrderID to

item_PurchaseOrderID

ResidenttaableName1:

load *

resident order where exists(PurchaseOrderID ,item_PurchaseOrderID );

ResidenttaableName1:

load *

resident order where exists(PurchaseOrderID ,item_PurchaseOrderID );

hope this helps