Issue encountered during reload process: Field 'supplier_key' not found

I hope you are all doing well. I am currently facing an issue during the reload process. Specifically, the error occurs when attempting to reload the 'ap_dummy_trx' tab, and I am receiving an error message stating that the field 'supplier_key' cannot be found.

After investigating the matter, it seems that the error may be related to the modifications made in the 'supplier_invoices' script, as the reload process works perfectly without these modifications. I am unsure of the exact cause of this error and would greatly appreciate any assistance in understanding and resolving this issue.

Here's the error message I am encountering:

Field not found error
Field 'supplier_key' not found

temp_existing_trx_key:
LOAD
    distinct trx_key as existing_trx_key
FROM
    D:\JDE\qvds\Entities\purchases.qvd
    (qvd)


Table not found
drop Table temp_existing_trx_key


supplier_invoices << PRODDTA.F0414 107,205 Lines fetched
CONV_RATE << PRODDTA.F0015 259 Lines fetched
CONV_RATE_SORTED << CONV_RATE 259 Lines fetched
CONV_RATE_SORTED-2 << CONV_RATE_SORTED 2,100 Lines fetched
CONV_RATE_SORTED-2 << CONV_RATE_SORTED 2,332 Lines fetched
TEMP_DISTINCT_MONTH_YEAR << supplier_invoices 107,205 Lines fetched
supplier_invoices 107,205 Lines fetched
Field 'supplier_key' not found
temp_existing_trx_key:
LOAD
    distinct trx_key as existing_trx_key
FROM
    D:\JDE\qvds\Entities\purchases.qvd
    (qvd)

I have included the script here below for reference:

sub read_supplier_invoices

    // Load supplier_invoices table
    supplier_invoices:
    LOAD 
        RNDOC & '|' & RNDCT & '|' & RNKCO as trx_key,
        RNDOC as accounting_invoice_number, 
        RNDOC as accounting_order_number,
        RMDOCM as accounting_document_number,
        RNPYID as accounting_line_number,
        RMDCTM as accounting_document_type, 
        null() as accounting_open_amount, 
        RNPAAP/100 as accounting_amount, 
        RNPAAP/100 as state_account_accounting_amount, 
        RNPFAP/100 as accounting_amount_by_currency, 
        RNCRCD as accounting_amount_currency,
        RNCRR as accounting_rate,
        RNMCU as accounting_batch_number, 
        PERIOD_DATE as accounting_trx_date,
        RNKCO as company_key,
        RNDCT as document_type,
        RNDOC as document_number,
        RNAN8 AS supplier_key,
        'ap' as accounting_trx_type,
        'invoice' as accounting_record_type,
        'D' as accounting_mvt_type,
        null() AS accounting_remark,
        'supplier_invoice' as accounting_record_source,
        null() as accounting_document_status
    FROM $(vQvd_Files_Location)PRODDTA.F0414.qvd (qvd)
    Where RNDCT <> 'PG';
    
CALL Conv_Rate
    
    
LEFT JOIN IntervalMatch(accounting_trx_date,accounting_amount_currency)
LOAD rate_effective_date, rate_effective_to_date,accounting_amount_currency Resident CONV_RATE_SORTED 
     where WildMatch(accounting_amount_currency,'lbp');


left join(supplier_invoices)
LOAD rate_from_amount,
//rate_from_amount_fd,
accounting_amount_currency, accounting_trx_date Resident CONV_RATE_SORTED;
DROP TABLE CONV_RATE_SORTED;


TEMP_DISTINCT_MONTH_YEAR_1:
LOAD DISTINCT
MonthName(accounting_trx_date) as TEMP_DATE,
supplier_key
Resident supplier_invoices;

JOIN(TEMP_DISTINCT_MONTH_YEAR_1)
LOAD DISTINCT supplier_key 
Resident supplier_invoices;


supplier_invoices:
NoConcatenate
LOAD *,
IF(RowNo() = 1 OR supplier_key <> Previous(supplier_key), FROM_DATE 
,IF(FROM_DATE = num(AddMonths(Previous(FROM_DATE),-1)) OR( FROM_DATE = PEEK(TO_DATE) and FROM_DATE = PREVIOUS(FROM_DATE)), FROM_DATE
,if(FROM_DATE = PREVIOUS(FROM_DATE),peek(TO_DATE), num(AddMonths(PREVIOUS(FROM_DATE),-1))))
)
AS TO_DATE,

rate_from_amount * state_account_accounting_amount as rate;

 
store supplier_invoices into $(vEntity_Location)supplier_invoices.qvd;

drop Table supplier_invoices;

endsub


I kindly request that you provide your assistance and insights to help me resolve this issue.

Thank you for your understanding and support.

I think your issue is with this part of the script:

supplier_invoices:
NoConcatenate
LOAD *,
IF(RowNo() = 1 OR supplier_key <> Previous(supplier_key), FROM_DATE 
,IF(FROM_DATE = num(AddMonths(Previous(FROM_DATE),-1)) OR( FROM_DATE = PEEK(TO_DATE) and FROM_DATE = PREVIOUS(FROM_DATE)), FROM_DATE
,if(FROM_DATE = PREVIOUS(FROM_DATE),peek(TO_DATE), num(AddMonths(PREVIOUS(FROM_DATE),-1))))
)
AS TO_DATE,

rate_from_amount * state_account_accounting_amount as rate;

I see that this is set up as a preceding load, whether intentional or not. If you're unfamiliar with that term, that is a way to load fields from an already loaded table without using RESIDENT and then DROP TABLE. Since your script is trying to load it as a Preceding Load, it is basically trying to load those fields, including supplier_key which appears first, from whatever table is below it (as far as where the Loads are written in the load script).

To rectify this, you should change it from a preceding load to a RESIDENT or FROM Load or whatever. Or if the preceding load was intentional, just make sure the table its Loading from actually has all of the necessary fields, including supplier_key.

I also notice that that table, [supplier_invoices], is named the same as the first table loaded in the script. Make sure you are naming your tables uniquely. If you intended on this table replacing that first one, you should do:

[supplier_invoices_new]:
NoConcatenate Load
    [...]
Resident [supplier_invoices];

Drop Table [supplier_invoices];
Rename Table [supplier_invoices_new] to [supplier_invoices];

Or something like that.