topic Re: Joining Tables in QlikView

Joining Tables

Anonymous — Thu, 22 May 2014 14:55:39 GMT

Hi Guys

I am joining 2 seperate data sources on a Vehicle Vin

Table 1:

Load

Vehicle.VINNumber as %VIN

from $(vQVDLOADPATH_Source1)Vehicle.qvd (qvd);

Table 2

LOAD

Vehicle.VIN AS %VIN,

* FROM $(vQVDLOADPATH_Source2)Vehicle.QVD (qvd);

What can i write so i only Load Unique results from Table 2

Because Tablle 1 has thousands rows of data and Table 2 only has 52 i only want to Dislpay 52 over the whole report.

Thanks all

Re: Joining Tables

Thu, 22 May 2014 14:57:15 GMT

Would this be a situation where a OUTER JOIN would be used?

Re: Joining Tables

Thu, 22 May 2014 14:57:59 GMT

Understanding Join, Keep and Concatenate

Re: Joining Tables

Anonymous — Thu, 22 May 2014 15:03:29 GMT

Hi Robert

No sorry cannot join the tables

Re: Joining Tables

chematos — Thu, 22 May 2014 15:09:18 GMT

Just duplicate the fields and use the field from the table you need.

Table1:

Load

Vehicle.VINNumber as %VIN,

Vehicle.VINNumber as Table1.VIN

from $(vQVDLOADPATH_Source1)Vehicle.qvd (qvd);

Table 2:

NoConcatenate

LOAD

Vehicle.VIN AS %VIN,

Vehicle.VINNumber as Table2.VIN

* FROM $(vQVDLOADPATH_Source2)Vehicle.QVD (qvd);

Re: Joining Tables

Anonymous — Thu, 22 May 2014 15:14:39 GMT

Would it matter there are pages of data between these two tables?

E.g

Table1:

Load

Vehicle.VINNumber as %VIN,

Vehicle.VINNumber as Table1.VIN

from $(vQVDLOADPATH_Source1)Vehicle.qvd (qvd);

Buyer:

LOAD * FROM $(vQVDLOADPATH)Buyer.QVD (qvd);

SaleAgreement:

LOAD * FROM $(vQVDLOADPATH)SaleAgreement.QVD (qvd);

SaleChannel:

LOAD * FROM $(vQVDLOADPATH)SaleChannel.QVD (qvd);

SaleChannel_history:

LOAD * FROM $(vQVDLOADPATH)SaleChannel_history.QVD (qvd);

Table 2:

NoConcatenate

LOAD

Vehicle.VIN AS %VIN,

Vehicle.VINNumber as Table2.VIN

* FROM $(vQVDLOADPATH_Source2)Vehicle.QVD (qvd);

Re: Joining Tables

Anonymous — Thu, 22 May 2014 15:18:41 GMT

Hi Adil,

In your case why do you required table 1 if you just want unique record from table2?

2nd if want unique record from both table without joining you can use inner keep which will give 2 separate table with unique record in both.

or you want records from table 2 you can go for Jose solution.

Thanks & Regards,

Kiran Kokade

Re: Joining Tables

Anonymous — Thu, 22 May 2014 15:20:36 GMT

HI Kiran

Table 2 contains data from a different datasource and contains data needed.

When i load this up all data is selected in the script i do not want to load 1,000,000 rows when i just need 52.

Thanks

Re: Joining Tables

Anonymous — Thu, 22 May 2014 15:22:57 GMT

Hi Adil,

Any reason to load data from table 1 then?

Thanks & Regards

Kiran Kokade

Re: Joining Tables

chematos — Thu, 22 May 2014 15:27:06 GMT

You can't avoid to load all the records from table1 in the script if you don't use a WHERE condition at the SQL sentence. That is independent on what do you want to show in your dashboard after the loading script. You really need to use a join like Kiran sais above if you want to discard rows from table1, but initially you must load the rows...