Getting the remaining results from a left join

[matthewp]

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

[lironbaram]

hi

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

[tresesco]

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.

[matthewp]

How would i do this?

[matthewp]

Hmm no this just loads in everything unfortunatly

[tresesco]

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

[matthewp]

Field not found - <INFromTab1>

from the concatenate table

does it need to be loaded in there?

[tresesco]

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