topic Re: multiple joins between tables in QlikView

multiple joins between tables

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am converting BO report which has below query into Qlikview, there are multiple joins available between two tables , how can I implement same in Qlikview and also column names used in join are not same

Select col1,col2

from table1, table2

where

table1.col1=table2.col2

and table1.col3=table2.col4

and table1.col5=table2.col6

Re: multiple joins between tables

squeakie_pig — Fri, 05 Aug 2016 10:34:54 GMT

If it is coming from a database you can load via sql

LOAD *;

SQL SELECT

col1,col2

from table1, table2

where

table1.col1=table2.col2

and table1.col3=table2.col4

and table1.col5=table2.col6

this is a great intro for understanding different joining options: Understanding Join, Keep and Concatenate

Re: multiple joins between tables

Anonymous — Fri, 05 Aug 2016 10:39:46 GMT

Hi, supossing your query works fine in your database you can just do all the joining in the database end:

TableName:

Load

;

SQL

Select col1,col2

from table1, table2

where

table1.col1=table2.col2

and table1.col3=table2.col4

and table1.col5=table2.col6

;

Re: multiple joins between tables

vinieme12 — Fri, 05 Aug 2016 10:51:16 GMT

You need to Associate the data with the composite key here, as this query is more of an inner join

Table1:

col1,

col3,

col5,

col1&'-'&col3&'-'&col5 as KeyCol

FROM XXXX;

INNER JOIN (Table1)

Table2:

col2,

col4,

col6,

col2&'-'&col4&'-'&col6 as KeyCol

FROM YYYY;

Re: multiple joins between tables

Gysbert_Wassenaar — Fri, 05 Aug 2016 11:01:32 GMT

Give the fields that should be used to join the tables the same name in both tables.

Or if you have a working sql statement then use that as-is. Qlikview will send it to the database server to let the database server execute the statement. Qlikview will then get the resulting records back and load those in a table.

Re: multiple joins between tables

rajuamet — Fri, 05 Aug 2016 11:10:25 GMT

Hi Vibhu Sharma,

Approach 1: Use sql select, so the you can apply joins using sql query from qlik itslef.

Approach 2: use Vineet Pujari's script.

PFA - Extended version of Approach 2.

Re: multiple joins between tables

Fri, 05 Aug 2016 13:29:15 GMT

Hi,

how do implement SQL in script based on multiple tables

I tried to select multiple tables in Create Select Statement window in script editor but it is not allowing me to do so

Re: multiple joins between tables

rajuamet — Fri, 05 Aug 2016 13:34:17 GMT

Hi Vibhu Sharma,

Take a quick look at this link

https://community.qlik.com/thread/133042