topic Associating Data from more than 1 Table in QlikView

Associating Data from more than 1 Table

Tue, 01 Sep 2009 18:11:42 GMT

I have created a Data Script with two different tables and a unique key. When I create a straight table I can see all fields in the properties but cannot report across the tables - i.e. I get all data from Table 1 and blank results for the fields from Table 2.

Any advice on what I'm doing wrong ?

Associating Data from more than 1 Table

lmonincx — Tue, 01 Sep 2009 21:35:04 GMT

HI,

Do you mean the content of the fields from the second table is empty?

Are you sure the content in you key's are inline ( match)?

Could you provide me a sample of your qvw to have a look?

Linda

Associating Data from more than 1 Table

Tue, 01 Sep 2009 21:51:37 GMT

Can you try using a left join when loading the script?

Table_Master:
LOAD
link_Key,
customer,
Product,
invoice_no
LineAmount,
CurrencyCode;
SQL SELECT *

FROM Table1;

Left join(Table_Master)
LOAD
link_Key,
description;
SQL SELECT *
FROM Table2;

Associating Data from more than 1 Table

Wed, 02 Sep 2009 12:12:28 GMT

In the original file, the fields are complete but when I try and report on them the second table data fields are either empty or I get two rows of independent data (one for each table)...In the eg. below, "Customer" is the "key".

Thanks for any help.

Toby

Customer	Insurance Limit USD	DSO	Next Review Date	Current (in USD)	Internal Rating
5070	0			4436401.11
5070		25	09/30/2009		75
5120			08/31/2009		75
5250	0			41469.36
5250		51	09/30/2009		75
5351			08/31/2009		75
5455			08/31/2009		75
5460	0			8474725.51
5460		97	09/30/2009		75
5489	0			385696.62
5489			09/30/2009		75
5754	0			1825230.02
5766	0			8420603.56
5776	8000000			2559556.65

AW:Re: Associating Data from more than 1 Table

biester — Wed, 02 Sep 2009 12:37:08 GMT

Hi,

can you at least post a snapshot of the data structure (Table Viewer)?

Rgds,
Joachim

AW:Re: Associating Data from more than 1 Table

Wed, 02 Sep 2009 12:55:06 GMT

Associating Data from more than 1 Table

Wed, 02 Sep 2009 15:22:22 GMT

Thanks for the advice - sorry, my Qlikview skills aren't that advanced that I can get this script to run..

AW:Re: Associating Data from more than 1 Table

biester — Thu, 03 Sep 2009 00:51:38 GMT

Hi,

well, in fact the key is a synthetic one (which should be avoided for performance reasons but nevertheless works) and not just "Customer" but "Customer" and "Country". I could imagine that you have Customer AND Country fields in both tables, but in ONE of them only Customer is filled (and country perhaps is NULL). Try to add the field "Country" to the table so you se what really IS being joined. Perhaps that helps.

Regards,
Joachim

AW:Re: Associating Data from more than 1 Table

Thu, 03 Sep 2009 17:57:55 GMT

Thanks for this,

Have tried it [as well as a left join - finally got the handle on that one] and get some better results but still not 100%. Have got an expert coming in on Monday to fix this for me so will see.

Thanks for everyone's assistance and advice.

Toby