Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
thnkgreen
Creator
Creator

INNER JOIN syntax

Hello my fellow QV enthusiasts.  I am having a time trying to figure out how to convert the SQL query (from Teradata) below into a QlikView load script.  My issue is that one of the tables, table1, has about 100 million records in it so it isn't feasible for me to load the entire table and then do a inner join to table 2.  Any ideas as to how this could be rewritten for QlikView I would greatly appreciate.

SELECT                   table1.first_name
                                ,table1.last_name
                                ,table1.date
                                ,table1.birthdate        
                                          
FROM                     TABLE_A table1
INNER JOIN     TABLE_B table2

ON                            table2.first_name = table1.first_name
                                AND table2.date = table1.date       
                                AND table1.valid = 1
                                AND table1.expir_dt =DATE'9999-12-31'                                                       
WHERE table2.wae_prd_dt = DATE'2016-08-01';

5 Replies
praveenavelagap
Creator III
Creator III

1. Load Table 2 first and make inner join  Table1 , So, All table1 records are not loaded!!

2. Try with KEEP

Are you having TPT connector to extract teradata data, so it will be fast!?

thnkgreen
Creator
Creator
Author

I am unaware of the TPT connector.  Is it available to personal edition users?

Not applicable

NO matter if you have many million of records YOUR problem its you JOIN first question:

Why Do you Join by Column DATE and First Name??? Do you have other Column to JOIN for example IdPovider or IdVendor, IdEmployment  etc, etc.

Check your Columns to JOIN!!!

Regards

Digvijay_Singh
Master III
Master III

May be like this -

1. First load Table1 and create new QVD of table 1.

2. Load  Table 1 from QVD and load table 2 with inner join and where clause like -

LOAD *

From Table1QVD

Inner join

Load *

From Table2

Where exists(expir_dt&'-'&valid,'9999-12-31-1')


You may need to create new field in table 1 to combine both of your fields which need comparison in exists in case it doesn't work as above, also date formatting needs to be verified.

Peter_Cammaert

Any reason why you want to forcibly move away from a SQL JOIN? Since the data doesn't need to be transferred before the JOIN, IMHO a SQL JOIN would offer at least a big performance advantage.