Retrieve values across multiple tables?

[nabilfa1]

Hello everyone, I'm new to QlikView and learned a lot already through this wonderful community. hope to find answer to my problem!.

I'm struggling to retrieve "Correct Values" from Multiple tables which i assume are correctly linked.

To explain what i need to achieve, There are 4 tables as follows:

1. Matter: Which are business legal cases.

2. Invoice: Invoice related to Matter ( A Matter can have multiple invoices from 1 or more vendors (1 - M relationship)

3 Vendor: this is the vendor Master list where multiple vendors can be associated to 1 or more Matter M-M relationship)

4 Matter-Invoice: This table is to Link Matter with Invoices

I'm trying to build a pivot chart that displays the following, for each vendor, the Matters assigned to the vendor and Invoices sent by this vendor related to each Matter.

Unfortunately,  the data results are not correct ( As attached below) which i somehow understand the logic behind. but i'm now desperate to find the right approach!!

As you may notice on the pivot chart, the invoices(INV1, INV2, INV3) are duplicated for V1 & V2 who are working on the same Matter (M1) Which should NOT be the case!

Attached an Example.

Thanks a lot for your Help!

Fady

[marcus_sommer]

Which one of the matchings V1 - M1 or V2 - M1 is correct and why?

I think it's either a matter of data-quality (duplicates from Matter_ID in Vendor) or you need a logic to determine which of the key-links is correct or not (and quite probably some more working-steps for them - maybe with period-fields like FromDate and ToDate).

- Marcus

[jonathandienst]

As the vendor is only linked to the invoice via the Matter_ID, and you can have multiple vendors per matter, there is no way of deciding which to which vendor the invoice should be assigned. You need a composite key of the vendor and Matter_ID, or you need a link table to associate the vendor, matter and invoice correctly.

[nabilfa1]

Thanks Jonathan!, Is it possible to show me an example how to create a link table in this example?

Also not sure if i created a composite key of Vendor_ID and Mater_ID,  how this will help?

Fady

[nabilfa1]

Hi Markus, actually both matchings V1-M1 and V2-M1 are correct, Also the matching between Matter and Invoice is Correct (i.e M1 is correctly linked to INV1, INV2 and INV3)

The problem here is the wrong match between the Vendor and Invoice! in other words the invoices are populated based on the Matter only while discarding the Vendor.

Fady

[jonathandienst]

I would probably go for a denormalised structure like this:

Matter:

Matter_ID

Matter_Name

...<more matter related stuff>...

Invoice:

Matter_ID

Vendor_ID

Vendor_Name

Invoice_Number

...<more invoice related stuff>...

QV works differently to a database and the optimal structure in QV is often/usually different to the db structure.

If you have a lot of vendor attributes, you could also structure like;

Matter:

Matter_ID

Matter_Name

...

Invoice:

Matter_ID

Vendor_ID

Invoice_Number

...

Vendor:

Vendor_ID

Vendor_Name

...