
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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';
- Tags:
- qlikview_scripting

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am unaware of the TPT connector. Is it available to personal edition users?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
