question about datamodel (link table)

amien — Tue, 07 Feb 2012 08:48:59 GMT

i have an orderitem table which contains a debtor_address

i have an order table which contains a customer_adress

i have a master table with all adresses. both can link to debtor and customer.

what i could do is to read the address table twice and create a different key for each one (one on debtor address and one for customer_address)

but can i do this using a link table? how would the script look like?

this is what i have now:

order table : AUTONUMBERHASH128(casenr,companycode) AS %case_ID

orderitem table : AUTONUMBERHASH128(casenr,companycode) AS %caseitem_ID,

link table:

order table / order item table using CONCATENATE (fieldname are renamed differenly to prevent SYN key)

casenr AS %casenr_ID,

companycode AS %companycode_ID,

AUTONUMBERHASH(casenr,companycode) AS %caseitem_ID / %case_ID

the result is, when i select a customer name, the debtor names disappear and the other way arround.

What is the proper way to achive this?

Thanks in advanced!

question about datamodel (link table)

stephencredmond — Wed, 08 Feb 2012 10:04:24 GMT

Hi,

If you concatenate the two tables like this, then the joins to customer address and debtor address will be logically on different rows so therefore will do what you are experiencing - select one and the others disappear.

Do you really need to analyse debtors and customers together like this? I would probably go for having separate address tables. Use Exists or Keep to reduce the number of records in the Master table to match the relevant customer or debtor information.

Stephen

topic question about datamodel (link table) in QlikView

question about datamodel (link table)

question about datamodel (link table)