topic Re: Connecting 2 tables in QlikView

Connecting 2 tables

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

i am triing to connect an Access database to the rest of my dataset which i load via csv.

Now - i think this is a very basic problem - but at the moment i dont know how to solve it.

So let's say via my csv i load customers. They have a customer ID. I give the name CID.

Now i load the access DB. In this DB there is a table and it also has a customer ID (but this table contains only a fraction of all my customers).

Now i thought if i rename the column in both tables to CID it should create a natural JOIN, right? But at the moment the result is more or less a crossproduct.

So how can i JOIN those 2 tables in a way, that the fraction of customers in my Access table gets connected to the rest of my dataset?

Re: Connecting 2 tables

Anil_Babu_Samineni — Fri, 14 Oct 2016 10:35:51 GMT

You can use Noconcatenate for those two tables

Re: Connecting 2 tables

sunny_talwar — Fri, 14 Oct 2016 10:48:34 GMT

Have you checked the join in the table viewer (Ctrl + T) to see if they are connected on CID or not?

Re: Connecting 2 tables

kkkumar82 — Fri, 14 Oct 2016 10:52:00 GMT

As Sunny said,

Can you give a screen shot of your data model to see if the tables are associated or not.

Re: Connecting 2 tables

Fri, 14 Oct 2016 11:12:03 GMT

Not quite what i want to have. Or at least it did not work.

Maybe it is too hard for me to explain. I try again:

I have 2 tables. Both share the same ID. But one table only has a fraction of the IDs of the other tables.

So now i load the smaller table from my ACCESS DB:

ODBC CONNECT32 TO [MS Access .....];

NoConcatenate

KZ2:

LOAD cust as kz2.cust,

custID as CID,

NNAME as kz2.name;

SQL SELECT *

FROM `KZ02`;

Now i have the CID already defined elsewhere. In this other table i have all my customers. In this new table i load right now- i only have a fraction of curstomers.

Now when i create a table box in QV and load this whole table.

I get a table with CID, kz2.cust, kz2.name.

The problem is , the table does not only show the CIDs from my ACCESS table, but shows all CIDs.

This results in a lot of lines where CID is defined, but the other 2 values are empty.

Now what i want is a table where CID is defined and shown only IF the other 2 values are there as well - or better said - only if the CID was also part of the original ACCESS file. But still the link to my other tables has to work.

For example: If i click in this table on the CID it should choose the correct customer from the big table.

Hope my explanatons are somehow understandable.

Re: Connecting 2 tables

Fri, 14 Oct 2016 11:21:19 GMT

Unfortunately i can not post a screenshot - but i am pretty sure the tables are associated.

Like i said in my last answer - now i have a giant table with

CID, kz2.cust, kz2.name

Though in the CID column - i have all CIDs from my other table which has all customers. I would like to only have the customers which were originally in this table. (I am talking about a table box). Maybe i just have to change something in the table box?

Right now i have a lot of these lines

CID, kz2.cust, kz2.name

333, 223,Josh <= ok

334,, <= not ok

335, 123, Carl <= ok

336, 445, Doris <= ok

337,, <= not ok

So in the table box i only want the lines which are entirely filled - like in my original table.

About the association - if i click on the CID it is linked to all my other tables. So association seems to work.

Re: Connecting 2 tables

Anonymous — Fri, 14 Oct 2016 12:41:20 GMT

You can try adding a Flag field to your script on the Access table

Access_List:
Load All_your_fields,
'Y' as Access_Flag
from yourAccessDb;

Then include it in your straight table to filter out the data you dont want to see be selecting Y on the Access_Flag field.