Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);;
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] );
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.
How would i do this?
Hmm no this just loads in everything unfortunatly
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()
Field not found - <INFromTab1>
from the concatenate table
does it need to be loaded in there?
Please note the field order change in exists() in my latest correction.