topic Re: Left Join Issue in QlikView

Left Join Issue

New-Qlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi.

I have two tables and doing left join

Items:

SQL SELECT
        CustID,
    OrderNo,
    OrderType,
    ReqNo,

ReqDate
FROM Table1
where
ReqDate>= '2016/02/26' and ReqDate<= '2016/03/26';

left join (Items)

SQL SELECT
        ProductMasterID,
    ProductName,
    ReqNo
FROM .Table2;

Table1 return 2lacs record and Table 2 retursn 4 lacs when we run this query in database

but in Qlikview

table 1 returns 2 lacs

where as table 2 is fetching more that 10 crores

so basically doing cross join.

Please guide what wrong I am doing.

TIA

Re: Left Join Issue

Anil_Babu_Samineni — Tue, 26 Jul 2016 13:51:18 GMT

Here,

How is your Data model

Re: Left Join Issue

New-Qlik — Tue, 26 Jul 2016 13:55:49 GMT

Just these two tables only

Re: Left Join Issue

New-Qlik — Tue, 26 Jul 2016 13:56:22 GMT

joined on basis of reqno

Re: Left Join Issue

Clever_Anjos — Tue, 26 Jul 2016 14:01:32 GMT

If those tables are into same database, keep your script making joins using SQL not Qlik.

Believe me, much more optimized

Re: Left Join Issue

Anil_Babu_Samineni — Tue, 26 Jul 2016 14:08:21 GMT

Items:

SQL SELECT
        CustID,
    OrderNo,
    OrderType,
    ReqNo,

ReqDate
FROM Table1
where
ReqDate>= '2016/02/26' and ReqDate<= '2016/03/26';

// For this you will get 1 month Data only

left join (Items)

concatenate / Join (Items)
SQL SELECT
        ProductMasterID,
    ProductName,
    ReqNo
FROM .Table2 group by ReqNo;

Final Out put is

CustID OrderNo OrderType ReqNo ReqDate ProductMasterID ProductName

Re: Left Join Issue

New-Qlik — Tue, 26 Jul 2016 17:47:48 GMT

These tables are coming from same database. but I didn't get your point

Re: Left Join Issue

New-Qlik — Tue, 26 Jul 2016 17:48:21 GMT

you are saying do concatenate instead of join?

Re: Left Join Issue

Clever_Anjos — Tue, 26 Jul 2016 17:54:11 GMT

SELECT
        CustID,
    OrderNo,
    OrderType,
    ReqNo,

ReqDate ,

ProductMasterID,

ProductName

FROM Table1

LEFT join Table2 on Table1.ReqNo = Table2.Reqno
where
ReqDate>= '2016/02/26' and ReqDate<= '2016/03/26';

Re: Left Join Issue

New-Qlik — Tue, 26 Jul 2016 17:57:12 GMT

this is database query .

In Qv also we write like this?

Re: Left Join Issue

Clever_Anjos — Tue, 26 Jul 2016 18:18:13 GMT

LOAD *;

SQL

SELECT
        CustID,
    OrderNo,
    OrderType,
    ReqNo,

ReqDate ,

ProductMasterID,

ProductName

FROM Table1

LEFT join Table2 on Table1.ReqNo = Table2.Reqno
where
ReqDate>= '2016/02/26' and ReqDate<= '2016/03/26';

Re: Left Join Issue

New-Qlik — Tue, 26 Jul 2016 18:46:17 GMT

Thanks Clever but this is not working.

We cant write like this in Qv. Syntax error

Re: Left Join Issue

New-Qlik — Tue, 26 Jul 2016 19:08:21 GMT

Just read that this was a bug in QV 12 SR1

But I am using QV12 only:(

Left join broken in QlikView 12 SR1?