topic Re: INNER JOIN syntax in QlikView

INNER JOIN syntax

Anonymous — Thu, 22 Sep 2016 17:26:06 GMT

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';

Re: INNER JOIN syntax

Anonymous — Thu, 22 Sep 2016 17:38:57 GMT

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!?

Re: INNER JOIN syntax

Anonymous — Thu, 22 Sep 2016 17:52:45 GMT

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

Re: INNER JOIN syntax

Thu, 22 Sep 2016 17:57:03 GMT

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

Re: INNER JOIN syntax

Digvijay_Singh — Thu, 22 Sep 2016 18:05:03 GMT

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.

Re: INNER JOIN syntax

Peter_Cammaert — Thu, 22 Sep 2016 20:05:28 GMT

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.