topic loading a table based on the field value of another table in QlikView

loading a table based on the field value of another table

Mon, 06 Apr 2015 04:43:36 GMT

Hi - I'm trying to load a table based on the value of a field in another table. Can it be done? I've searched the Community for an answer. I found many similar questions, but no Correct Answer.

Here's a simplified example:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Table1:

Load CustomerID, CustomerCity;

SQL Select * from Customers where CustomerCity in 'Los Angeles';

Table2:

Load InvoiceID, CustomerID, InvoiceAmount

Where Exists (CustomerID, Table1>CustomerID); // can there be a Where Exists clause that // filter this load based on the CustomerID from Table1?

SQL Select * from Invoices;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks!

Re: loading a table based on the field value of another table

anbu1984 — Mon, 06 Apr 2015 05:21:05 GMT

Yes you can filter using Exists(). Try this

Table1:

Load CustomerID As CustID, CustomerCity;

SQL Select * from Customers where CustomerCity in 'Los Angeles';

Table2:

Load InvoiceID, CustomerID As CustID, InvoiceAmount

Where Exists (CustID, CustomerID);

SQL Select * from Invoices;

Re: loading a table based on the field value of another table

sujeetsingh — Mon, 06 Apr 2015 05:23:37 GMT

Greg,

Just left join table 1 with the table 2

Table1:

Load CustomerID, CustomerCity;

SQL Select * from Customers where CustomerCity in 'Los Angeles';

left join ()

Load InvoiceID, CustomerID, InvoiceAmount

;

FinalData:

Load *

resident where CustomerID <> null();

Left join will work fine for this no need to quote any condition .

Re: loading a table based on the field value of another table

jagan — Mon, 06 Apr 2015 05:24:20 GMT

Hi,

Try like this

Using Exists:

Table1:

Load CustomerID, CustomerCity;

SQL Select * from Customers where CustomerCity in 'Los Angeles';

Table2:

Load InvoiceID, CustomerID, InvoiceAmount

Where Exists (CustomerID);

SQL Select * from Invoices;

Using Keep()

Table1:

Load CustomerID, CustomerCity;

SQL Select * from Customers where CustomerCity in 'Los Angeles';

Table2:

Left Keep(Table1)

Load InvoiceID, CustomerID, InvoiceAmount;

SQL Select * from Invoices;

Using Join()

Table1:

Load CustomerID, CustomerCity;

SQL Select * from Customers where CustomerCity in 'Los Angeles';

Table2:

Load InvoiceID, CustomerID, InvoiceAmount;

SQL Select * from Invoices;

INNER JOIN(Table2)

LOAD CustomerID

RESIDENT Table1;

Hope this helps you.

Regards,

Jagan.

Re: loading a table based on the field value of another table

ziadm — Mon, 06 Apr 2015 05:55:29 GMT

Yes .. Left Join will load only CustomerID that have invoices in the invoice files.

Re: loading a table based on the field value of another table

Mon, 06 Apr 2015 06:33:17 GMT

Thanks Anbu. It works perfectly!

Re: loading a table based on the field value of another table

Mon, 06 Apr 2015 06:34:55 GMT

I've only tried Anbu's solution and it worked great. Haven't had a chance to try other solutions yet but thanks for the great replies!

Re: loading a table based on the field value of another table

jagan — Mon, 06 Apr 2015 07:04:22 GMT

Hi,

Not sure why you are renmaing CustomerID, without renaming also works

Table1:

Load CustomerID, CustomerCity;

SQL Select * from Customers where CustomerCity in 'Los Angeles';

Table2:

Load InvoiceID, CustomerID, InvoiceAmount

Where Exists (CustomerID);

SQL Select * from Invoices;

Regards,

Jagan.

Re: loading a table based on the field value of another table

petter — Mon, 06 Apr 2015 07:41:41 GMT

I know this is not exactly what you asked for but it might suit your use-case anyway:

If your Customers table and your Invoices table are located in the same SQL database I would definitely recommend to limit the data transferred to a minimum by doing something like this

[Invoices]:
LOAD
     InvoiceID,
     CustomerID,
     InvoiceAmount;
SQL
     SELECT * FROM Invoices
     WHERE CustomerID IN ( SELECT CustomerID FROM Customers WHERE CustomerCity IN 'Los Angeles' )
;

Here you will simplify the QlikView code and it will limit the transfer of rows to what you actually need. This might have a significant positive impact on how much time and resources your script will impose on the source system and also on your QlikView solution.

(Of course you might already have thought about this):

I suggest that you get rid of the SELECT * as soon as you know which columns to bring over to QlikView. That will also limit the columns to the necessary minimum and speed up your load.