topic Connections between tables in QlikView

Connections between tables

Wed, 18 May 2011 06:43:29 GMT

Hi,

I have three tables that I want to join in a good way.

Table 1: purchase (purchaseID, customerID) -> customerID is a unique number to identify a customer

Table 2: customer (customerID, customerID_2) -> one customer could have many customerID, therefor a customerID_2 could have many customerID

Table 3: account (accountID, customerID_3) -> customerID_3 contains both customerID and customerID_2, and one accountID could only have one customerID_3

What I want to do now is to find out all purchaseID connected to a accountID. How can I solve this the best way using customer as a connection table. The best thing would be if instead putting everything in one table, have the three tables separeted with connections between them.

Thanks ahead!

Connections between tables

montubhardwaj — Wed, 18 May 2011 07:22:13 GMT

Please try the below:

Connections between tables

Wed, 18 May 2011 07:55:42 GMT

Hi,

It did work a bit, but im missing some accountID.

Just to clarify;

- all purchaseID have one customerID

- one customerID_2 could have many customerID, these are not shown in the purchase table

- in the account table one accountID have a connection to either a customerID or a customerID_2, these are stored as customerID_3

When I use your script I only get the accountID which have a connection to a customerID but not to those accountID which have the customerID_2 in the column customerID_3.

Is it possible to have these three tables separeted and not joined in only one table, because there is other columns in each tables that we want to use.

Thanks ahead!

Connections between tables

montubhardwaj — Wed, 18 May 2011 08:19:56 GMT

ok... So if I am getting you right, you key field should be this way so that it will cover all the Customer Ids.

purchase:

Load purchaseID as ID,

customerID_Pur

From purchase_table;

customer:

Left join

Load customerID as ID,

customerID_2_Cust

From customer_table;

account:

Left join

Load accountID as ID,

customerID_3 _Acct

From account_table;

If it is not working, maybe I got your requirements wrongly.

Connections between tables

Wed, 18 May 2011 08:34:09 GMT

Sorry, maybe Im not clear enough . This is how it looks like now.

purchase:

Load

purchaseID,

customerID

From purchase_table;

customer:

Load

customerID,

customerID_2

from customer_table;

account:

Load

accountID,

customerID_3

From account_table;

1) one purchaseID has only one customerID

2) one customerID can only be connected to one customerID_2 or null

3) one accountID can have many purchaseID

4) one accountID has a connection to either one customerID or customerID_2 not both, and that ID is stored as customerID_3

Problem 1 is that we don't know what customerID is connected to what customerID_2 throught the purchase_table. We need a connection between purchase_table and customer_table to know that, this is easy done with a join on customerID.

Problem 2 is that customerID_3 in account_table contains both customerID and customerID_2 in the same column. Its easy to find out all accountID that is using customerID as customerID_3 and from that find out all purchaseID. But we also want to find out which accountID that is connected to a customerID_2 and get their purchaseID.

Puh, its not simple to explain this

Connections between tables

montubhardwaj — Wed, 18 May 2011 09:57:12 GMT

For the 1st two tables, i ll approach this way i.e. rather than doing a QV JOIN,I will join explicitly so that both Purchase and Customer tablesare in 1 QV table.

By using below method, I think you should be able to find out which accountID that is connected to a customerID_2 and get their purchaseID.

Hope I am making some sense

Regards,

Sharma