loading a table based on the field value of another table

[#1]

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!

[anbu1984]

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;

[sujeetsingh]

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 .

[jagan]

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.

[ziadm]

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

[#6]

Thanks Anbu.  It works perfectly! 

[#7]

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!

[jagan]

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.

[petter]

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.