topic Re: WHERE Clause for both tables in QlikView

WHERE Clause for both tables

matthewp — Fri, 18 Dec 2015 15:35:01 GMT

I want to add a where clause that uses both tables.

this statement loads fine

TABA:

LOAD [TABA.BNumber] AS [Number],

[TABA.Status],

[TABA.I Number] AS [I No],

[TABA.reference] AS [App Ref],

[TABA.InvoiceType] AS [InvoiceType]

FROM

D:\QlikView\QVD\TABA\TABA.QVD

(qvd);

LEFT JOIN(TABA)

IFS:

LOAD [TABB.I Number] AS [B I Number],

[TABB.IFS I Amount],

[TABB.Name] AS [Name],

[TABB.DEL] AS [Del Locations],

[TABB.MIS I Ref] AS [I No]

FROM

D:\QlikView\Live\QVD\TABB\TABB.QVD

(qvd)

WHERE PurgeChar(TABB.Name,'*') >= '33000';

I want to add something like TABA.InvoiceType <> LEFT(TABB.DEL,5) but i get an error.

how can i do this

Re: WHERE Clause for both tables

sunny_talwar — Fri, 18 Dec 2015 16:56:18 GMT

May be this:

TABA:

LOAD [TABA.BNumber] AS [Number],

[TABA.Status],

[TABA.I Number] AS [I No],

[TABA.reference] AS [App Ref],

[TABA.InvoiceType] AS [InvoiceType]

FROM

D:\QlikView\QVD\TABA\TABA.QVD

(qvd);

LEFT JOIN(TABA)

LOAD [TABB.I Number] AS [B I Number],

[TABB.IFS I Amount],

[TABB.Name] AS [Name],

[TABB.DEL] AS [Del Locations],

[TABB.MIS I Ref] AS [I No]

FROM

D:\QlikView\Live\QVD\TABB\TABB.QVD

(qvd)

WHERE PurgeChar(TABB.Name,'*') >= '33000';

TABA1:

NoConcatenate

LOAD *

Resident TABA

Where TABA.InvoiceType <> Left(TABB.DEL,5);

DROP Table TABA;

Re: WHERE Clause for both tables

Fri, 18 Dec 2015 17:19:50 GMT

you will need to do a resident load of the TABA table in order to use the where since the field you are using in your where clause has not ben defined yet

or you can try

Load *
// loads data for specific invoice types
where TABA.InvoiceType <> LEFT(TABB.DEL,5)

prior to your table load