topic Re: Inner Join in QlikView

Inner Join

Wed, 28 Oct 2015 08:02:07 GMT

I have 2 tables

Table 1 contains the following and I have put in a where clause to reduce down the number of records being loaded.

VBAP:

LOAD MVGR1 As MatGrp1,
WERKS As Plant,
VBELN As SalesOrder,
//POSNR As LineItem,
MATNR As MatNo,
PRODH As ProdH,
if(PRODH = '539017' or PRODH = '539018' or PRODH = '539021' or PRODH = '539002' or PRODH = '5A9017' or PRODH = '5A9018' or PRODH = '5A9021' or PRODH = '5A9002' ,'Spares','Service') As ProdhType,
NETWR As SalesOrderValue,
WAERK As Currency,
ZZRGIDAT As CustReqDate,
ZZCCGDAT As CustCommitGIDate,
[Plant Region] As Region,
Market2 As Market

FROM

(qvd)

Where
//ZZRGIDAT > '08/01/2014'

PRODH >= '539001' and PRODH <= '539021'
or
PRODH = '539024'
or
PRODH = '539028'
or
PRODH >= '539030' and PRODH <= '539035'
or
PRODH >= '539038' and PRODH <= '539042'
or
PRODH >= '5A9001' and PRODH <= '5A9021'
or
PRODH = '5A9024'
or
PRODH = '5A9028'
or
PRODH >= '5A9030' and PRODH <= '5A9035'
or
PRODH >= '5A9038' and PRODH <= '5A9042'
;

Table 2 contains the following - I originally linked the 2 tables using a left join with the common field being VBELN, however that was causing me problems as table 1 only has 200,000 records but table 2 has over 7 million records.

I want to only load the records in table 2 that appear in table 1, I thought the way to do this was to use an inner join but when I do this I still see 7 million records being loaded in the data load. I'm not sure if I'm approaching this correctly

VBAK:

LOAD VBELN As SalesOrder,
[Sales Organization] As SalesOrg,
VKBUR As SalesOffice,
KUNNR As SoldToParty

FROM
[D:\SourceDocuments\PRODQLI

Re: Inner Join

settu_periasamy — Wed, 28 Oct 2015 08:12:36 GMT

Hi Andrew,

Try to use the

Where Exists(VBELN);

Re: Inner Join

PradeepReddy — Wed, 28 Oct 2015 08:13:55 GMT

1) we can use Exists function...

VBAP:

Load ...

From
(qvd)

where (condition1 and condition2) OR (condition3 and condition4) OR... ;

VBAK:

Load VBELN As SalesOrder,

....,

From ..

where exists(VBELN od second table ,SalesOrder of First Table);

2) We can use Inner join also

Re: Inner Join

sujeetsingh — Wed, 28 Oct 2015 08:15:31 GMT

USE INNER JOIN OF QLIKVIEW

Table1:

Load

VBELN

from xyz;

inner join

Load *,VBELN

from Table2;

Re: Inner Join

Anonymous — Wed, 28 Oct 2015 08:17:31 GMT

iy ou Need one target table then do a oleft join

left join(Table1)

LOAD VBELN As SalesOrder,
[Sales Organization] As SalesOrg,
VKBUR As SalesOffice,
KUNNR As SoldToParty
FROM
[D:\SourceDocuments\PRODQLI

One question: does your where clause in table 1 works? I would use brackets to differentuate between or/and

Re: Inner Join

jonathandienst — Wed, 28 Oct 2015 08:20:02 GMT

A file load will always load the entire file before applying any filtering - even with a qvd - as Qlikview needs to read the file to apply the filter.

Only a SQL query can filter the data before being loaded into Qlikview because the filtering is done by the DBMS, not by Qlikview.

Re: Inner Join

Wed, 28 Oct 2015 08:23:24 GMT

Thanks

Do I still use the Inner Join and where in the script do I put the where clause, I tried this but it loaded zero records

VBAK:

Inner join
LOAD VBELN As SalesOrder,
[Sales Organization] As SalesOrg,
VKBUR As SalesOffice,
KUNNR As SoldToParty

FROM

(qvd)

where Exists (VBELN)
;

Re: Inner Join

settu_periasamy — Wed, 28 Oct 2015 08:36:57 GMT

Hi Andrew,

the Where Exists should be like this

Where Exists(VBELN,VBELN);

I think, in this case left join will work as already mentioned by HRLinder

Re: Inner Join

Wed, 28 Oct 2015 08:41:48 GMT

I know have this

VBAK:

left join
LOAD VBELN As SalesOrder,
[Sales Organization] As SalesOrg,
VKBUR As SalesOffice,
KUNNR As SoldToParty

FROM

(qvd)

where Exists (VBELN,VBELN)
;

But the VBAK table loads no records

Re: Inner Join

himanshi — Wed, 28 Oct 2015 11:34:32 GMT

hii ,

you can use left keep instead of left join this will give the records of left table and matching records of right table.

Regards.

Re: Inner Join

neha_sri — Wed, 28 Oct 2015 11:45:14 GMT

Hi,

you can put the table that contains 200,000 records on top and then you can write left join and the table containing 7 million records.

Regards,

Neha