Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
matthewp
Contributor III

WHERE Clause for both tables

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

2 Replies

Re: WHERE Clause for both tables

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;

Not applicable

Re: WHERE Clause for both tables

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

Community Browser