topic Re: sql and qlikview join in QlikView

sql and qlikview join

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to implement replica of BO report in Qlikview which has below SQL, I have loaded the two tables , can I link two tables in Qlikview by renaming column same as join condition mentioned in SQL query

Select col1,col2

From table1,table2

where

table1.col1=table2.col2

Re: sql and qlikview join

Wed, 10 Aug 2016 14:46:14 GMT

Implement sql in script editor

Re: sql and qlikview join

kumarkp412 — Wed, 10 Aug 2016 14:48:54 GMT

Hi Vibhu,

can you elaborate your question?

Based on my understanding ,

create a script like this.

table1:

load

col1 as col

--;

select

from-----

left join(table1)

load

col2 as col

--;

select

from--

Re: sql and qlikview join

maxgro — Wed, 10 Aug 2016 14:50:24 GMT

Rename (alias) col2 to col1.

Table1:

select

col1,

....

Table2:

select

col2 as col1

.....

After the reload, in the table viewer (ctrl-t) you should have the 2 tables associated by col1

Re: sql and qlikview join

jpenuliar — Wed, 10 Aug 2016 14:56:17 GMT

Have a look here:

Understanding Join, Keep and Concatenate

Re: sql and qlikview join

jpenuliar — Wed, 10 Aug 2016 14:58:03 GMT

its also possible to use SQL joins(and other SQL stuffs) within Qlikview Script

Re: sql and qlikview join

vishsaggi — Wed, 10 Aug 2016 14:58:27 GMT

Hello Vibhu,

This is a duplicate thread. Please close one of them so that other people can benefit if your query is answered. Your same question thread is : shared in rodrigo msg above ?

Thanks,
V.

Re: sql and qlikview join

Wed, 10 Aug 2016 15:53:30 GMT

Hi,

I observed above approach is not giving correct result

Re: sql and qlikview join

Wed, 10 Aug 2016 15:53:51 GMT

Hi,

I have closed other thread

Re: sql and qlikview join

Wed, 10 Aug 2016 15:54:45 GMT

Can you suggest options to do that please

Re: sql and qlikview join

jpenuliar — Wed, 10 Aug 2016 16:27:58 GMT

Hi Vibhu,

provided you have created your ODBC connection, your sql join may look like below:

test:

SQL

SELECT

table1.column1,

table2.column2...

FROM

table1,

table2

Where

table1.common_field (+)= table2.common_field

;

Re: sql and qlikview join

vishsaggi — Wed, 10 Aug 2016 16:38:09 GMT

What is your expected output, any chance of sending that information to us?

I usually do this if am using SQL databases.

LOAD *;

SQL Select T1.Col1, T1.Col2,

T2.Col1, T2.Col2

FROM Table1 T1

LEFT JOIN or INNER JOIN

Table2 T2

ON T1.Col1 = T2.Col1 ;

Re: sql and qlikview join

maxgro — Wed, 10 Aug 2016 18:35:31 GMT

post a small example (.qvw) to demonstrate the problem

Re: sql and qlikview join

Wed, 10 Aug 2016 21:37:17 GMT

Hi,

I got the answer...means we can manually type the SQL after Load statement , am I correct?

Re: sql and qlikview join

vishsaggi — Wed, 10 Aug 2016 21:39:29 GMT

Yes, with a SQL clause before Select.

Re: sql and qlikview join

Thu, 11 Aug 2016 01:40:20 GMT

Hi ,

I have below SQL query which I need to implement in Qlikview , I have created qvd for each table with incremental load applied on each qvd, can I implement above mentioned approach for implementing below query means first load data from qvd using LOAD statement for each table and then implement SQL joins between tables along with conditions as shown below after load statement

SQL:

Select table1.col1, table2.col2,table3.col3

from table1,table2,table3

where table1.col1=table2.col2

and table1.col2=table2.col4(+)

and table1.col3=table2.col6(+)

and table2.col1=table3.col2(+)

and table2.col3=table3.col4(+)

and table2.col5=table3.col6(+)

and table1.col5='ABC'

and table2.col5='ABC'

and table3.col5='ABC'

Re: sql and qlikview join

vishsaggi — Thu, 11 Aug 2016 02:00:17 GMT

No i did not realize your are getting tables from QVD's. SQL clause works for any relational databases with a connecting string. I doubt if it works like that. How many qvd's you have and you dont need a SQL when we can form relations in Qlikview by creating a star schema with facts and dimension tables. What exactly you are trying to achieve can you please elaborate a little ?

Re: sql and qlikview join

Thu, 11 Aug 2016 09:50:12 GMT

we are creating replica of BO report in qlikview , we have implemented qvd with incremental load for some tables, database is oracle and it is ODS