topic Re: Getting the remaining results from a left join in QlikView

Getting the remaining results from a left join

matthewp — Mon, 05 Dec 2016 10:40:02 GMT

So i have a small scrip that loads in 2 qvds by a left join:

TABG:

LOAD [D Code],

[I Number],

[I Date],

[C Number],

Currency,

[G Amount],

[VAT Amount],

[Total Amount],

[T Date]

FROM

$(vGPath)\TABG.qvd

(qvd);

LEFT JOIN(TABG)

TABI:

LOAD C_ID,

C_NAME,

I_TYPE,

I_NO AS [I Number],

I_DATE,

T_DATE,

CURRENCY,

CURRENCY_RATE,

TOTAL_NET_VALUE,

TOTAL_VAT_VALUE,

TOTAL_INV_VALUE

FROM

$(vIPath)\TABI.qvd

(qvd);

Which gets me the results where there is a match on [I Number] in both tables.

What I want is to separately load (under a different table name) the results from TABG that don't have a matching [I Number] in TABI.

Something like:

TABG:

LOAD [D Code],

[I Number],

[I Date],

[C Number],

Currency,

[G Amount],

[VAT Amount],

[Total Amount],

[T Date]

FROM

$(vGPath)\TABG.qvd

(qvd)

WHERE [I Number] NOT IN

LOAD

I_NO AS [I Number]

FROM

$(vIPath)\TABI.qvd

(qvd);;

Re: Getting the remaining results from a left join

lironbaram — Mon, 05 Dec 2016 10:42:57 GMT

you can do something like this

LOAD distinct

I_NO

FROM

$(vIPath)\TABI.qvd

(qvd);;

TABG:

LOAD [D Code],

[I Number],

[I Date],

[C Number],

Currency,

[G Amount],

[VAT Amount],

[Total Amount],

[T Date]

FROM

$(vGPath)\TABG.qvd

(qvd)

WHERE not exists(I_NO , [I Number] );

Re: Getting the remaining results from a left join

tresB — Mon, 05 Dec 2016 10:46:38 GMT

Ideally you should load the unmatched items in the same table with a flag field that distinguishes 'Matched' and 'Unmatched'. Otherwise, in a separate table you have to rename fields (or use Qualify) to avoid synthetic keys.

Re: Getting the remaining results from a left join

matthewp — Mon, 05 Dec 2016 10:49:23 GMT

How would i do this?

Re: Getting the remaining results from a left join

matthewp — Mon, 05 Dec 2016 10:49:35 GMT

Hmm no this just loads in everything unfortunatly

Re: Getting the remaining results from a left join

tresB — Mon, 05 Dec 2016 11:13:28 GMT

Try something like:

TABG:

LOAD [D Code],

     [I Number],

     [I Date],

     [C Number],

     Currency,

     [G Amount],

     [VAT Amount],

     [Total Amount],

     [T Date]

FROM

$(vGPath)\TABG.qvd

(qvd);

LEFT JOIN(TABG)

TABI:

LOAD C_ID,

     C_NAME,

     I_TYPE,

     I_NO AS [I Number],

     I_NO AS INFromTab1,

     I_DATE,

     T_DATE,

     CURRENCY,

     CURRENCY_RATE,

     TOTAL_NET_VALUE,

     TOTAL_VAT_VALUE,

     TOTAL_INV_VALUE,

     'Matched' as Flag



FROM

$(vIPath)\TABI.qvd

(qvd);

Concatenate

LOAD C_ID,

     C_NAME,

     I_TYPE,

     I_NO AS [I Number],

     I_DATE,

     T_DATE,

     CURRENCY,

     CURRENCY_RATE,

     TOTAL_NET_VALUE,

     TOTAL_VAT_VALUE,

     TOTAL_INV_VALUE,

     'Unmatched' as Flag



FROM

$(vIPath)\TABI.qvd

(qvd) where not exists (INFromTab1, I_NO );

Edit: corrected the field order in exists()

Re: Getting the remaining results from a left join

matthewp — Mon, 05 Dec 2016 11:22:25 GMT

Field not found - <INFromTab1>

from the concatenate table

does it need to be loaded in there?

Re: Getting the remaining results from a left join

tresB — Mon, 05 Dec 2016 11:24:01 GMT

Please note the field order change in exists() in my latest correction.