Inner Join

[#1]

Hi

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

[settu_periasamy]

Hi Andrew,

Try to use the

Where Exists(VBELN);

[PradeepReddy]

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

[sujeetsingh]

USE INNER JOIN OF QLIKVIEW

Table1:

Load

,*

VBELN

from xyz;

inner join

Load *,VBELN

from Table2;

[Anonymous]

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

[jonathandienst]

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.

[#7]

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

[settu_periasamy]

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

[#9]

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

[himanshi]

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.