重载'ap_dummy_trx'时出现Field 'supplier_key'未找到错误求助

修改supplier_invoices脚本后，重载'ap_dummy_trx'标签出现Field 'supplier_key' not found错误，未修改前流程正常。

错误信息 #

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)

相关脚本 #

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

问题分析与解决 #

核心问题 #

重新创建supplier_invoices表的NoConcatenate LOAD语句未指定数据源，QlikView无法找到包含supplier_key的表来加载数据。

修复步骤 #

1. 在NoConcatenate的LOAD语句末尾添加Resident supplier_invoices，指定从已存在的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
Resident supplier_invoices; // 添加此行指定数据源

2. 额外检查点：
   • 确认Conv_Rate子过程中是否正确生成了FROM_DATE字段，否则IF语句会触发新的字段未找到错误
   • 若TEMP_DISTINCT_MONTH_YEAR_1表后续未被使用，建议添加DROP TABLE TEMP_DISTINCT_MONTH_YEAR_1;清理临时表，避免潜在冲突

内容的提问来源于stack exchange，提问作者MarioGB