Extract Script Refresh Error - Field Not Found

[ryanfoster84]

Hi 

We have an Extract script that loads daily and has been for the past year without issues but yesterday it stopped refreshing with the following error:

20250227T085006.186+0000 Error: Field 'TRADE_MONTH' not found
20250227T085006.672+0000 Execution Failed
20250227T085006.708+0000 Execution finished.

There are two tabs in the Extract Script:

REVENUE_ANALYTIC_VOLUME_REVENUE_INCREMENTAL:
LOAD
*
FROM
$(vQVD)REVENUE_ANALYTIC_VOLUME_REVENUE.qvd(qvd)
WHERE
Left([Year Month],4) >= '$(vRunDateStartYear)' AND [Year Month] <= '$(vRunDateYYYYMM)';

This loads the data from 202301 until 202410

The next tab then does this

REVENUE_ANALYTIC_VOLUME_REVENUE_INCREMENTAL:

Concatenate(REVENUE_ANALYTIC_VOLUME_REVENUE_INCREMENTAL)

LOAD

TRADE_MONTH AS [Year Month], 

PARENT_FIRM_ID AS [Parent Firm ID],

BILL_FIRM_ID AS [Bill Firm ID],

IF(IsNull(EXECUTING_FIRM_NUMBER)=-1,'Not Available',EXECUTING_FIRM_NUMBER)

AS [Executing Firm ID],

ACCOUNT_ID AS [Account ID],

ACCOUNT_NUMBER_EFS as [Account Number EFS],

MAIN_ACCOUNT_ID AS [Main Account ID],

OPERATOR_ID AS [Operator ID],

PARTY_ATTRIBUTE_KEY AS [Party Attribute Key],

    INCENTIVE_PROGRAM AS [Incentive Program],

FEE_TYPE AS [Fee Type],

IF(VENUE='CPC','ClearPort',

IF(VENUE='GBX','Electronic',

IF(VENUE='EXPIT','ExPit',

IF(VENUE='*','Venue NA',

IF(VENUE='PIT','Pit',VENUE))))) AS Venue,

IF(EXCHANGE_CODE='CBT','CBOT',EXCHANGE_CODE)

AS [Exchange Code],

PRODUCT_ID AS [Product ID],

IF(IsNull(AGGRESSOR_IND)=-1,'Not Available',AGGRESSOR_IND)

AS [Aggressor Indicator],

SHIFT AS Shift,

MEMBER_CUSTOMER_INDICATOR AS [Member Customer Indicator],

SENDER_LOCATION AS [Country of Origin Code],

CPC_TRDR_SENDER_LOCATION AS [CPC Country of Origin Code],  

VOLUME AS Volume,

REVENUE AS Revenue,

HOUSE_REVENUE AS [House Revenue], 

CLEARED_REVENUE AS [Cleared Revenue], 

HOUSE_VOLUME AS [House Volume],

CLEARED_VOLUME AS [Cleared Volume], 

SELF_MATCH_INDICATOR AS [Self Match Indicator], 

SELF_MATCH_VOLUME AS [Self Match Volume], 

SELF_MATCH_FEE_QUANTITY AS [Self Match Fee Quantity],

REVENUE_ADJUSTMENT AS [Revenue Adjustment],

CUSTOMER_SUB_NAME AS [Customer Sub Name],

BTIC_IND as [BTIC Indicator],

RETAIL_FIRM_NAME AS [Retail Firm Name],

RETAIL_INDICATOR AS [Retail Indicator],

ACCOUNT_OWNER AS [Account Owner],

ICN_PRELIMINARY AS [Internal Common Name Preliminary],

VEND_DESC AS Vendor,

SPREAD_CODE AS [Spread Code],

TRADE_SUB_TYPE AS [Trade Sub Type],

EMAIL_ADDRESS AS [Email Address]

;

SQL SELECT TRADE_MONTH

, PARENT_FIRM_ID

, BILL_FIRM_ID

, EXECUTING_FIRM_NUMBER    

and this loads the latest four months into the qvd. 

But i am now getting an error 20250227T085006.186+0000 Error: Field 'TRADE_MONTH' not found
20250227T085006.672+0000 Execution Failed
20250227T085006.708+0000 Execution finished.

Nothing has changed and this started happening randomly. 

Qlikview version 

12.70.20200.0

[PrashantSangle]

did you reconfirm with db team that this field "TRADE_MONTH" is in table or not?? because as we can see this field is coming from your SQL script

Regards,

Prashant Sangle

[ryanfoster84]

I can confirm this filed is in the DB and the SQL runs in Toad without issues

[ryanfoster84]

SQL SELECT TRADE_MONTH
, PARENT_FIRM_ID
, BILL_FIRM_ID
, EXECUTING_FIRM_NUMBER
, ACCOUNT_ID
, ACCOUNT_NUMBER_EFS
, MAIN_ACCOUNT_ID
, OPERATOR_ID
, PARTY_ATTRIBUTE_KEY
, INCENTIVE_PROGRAM
, FEE_TYPE
, VENUE
, EXCHANGE_CODE
, PRODUCT_ID
, AGGRESSOR_IND
, SHIFT
, MEMBER_CUSTOMER_INDICATOR
, SENDER_LOCATION
, CPC_TRDR_SENDER_LOCATION
, INTERNAL_PRIMARY_BUS_ACTIVITY
, nvl(SUM(VOLUME),0) AS VOLUME
, nvl(SUM(REVENUE),0) AS REVENUE
, nvl(SUM(HOUSE_REVENUE),0) AS HOUSE_REVENUE
, nvl(SUM(CLEARED_REVENUE),0) AS CLEARED_REVENUE
, nvl(SUM(HOUSE_VOLUME),0) AS HOUSE_VOLUME
, nvl(SUM(CLEARED_VOLUME),0) AS CLEARED_VOLUME
, SELF_MATCH_INDICATOR AS SELF_MATCH_INDICATOR
, nvl(SUM(SELF_MATCH_VOLUME),0) AS SELF_MATCH_VOLUME
, nvl(SUM(SELF_MATCH_FEE_QUANTITY),0) AS SELF_MATCH_FEE_QUANTITY
, nvl(SUM(REVENUE_ADJUSTMENT),0) AS REVENUE_ADJUSTMENT
, CUSTOMER_SUB_NAME
, BTIC_IND
, RETAIL_FIRM_NAME
, RETAIL_INDICATOR
, ACCOUNT_OWNER
, ICN_PRELIMINARY
, VEND_DESC
, SPREAD_CODE
, TRADE_SUB_TYPE
, EMAIL_ADDRESS
FROM (

SELECT

/*+ use_hash(TRADE_FEES process_dates) */

TO_CHAR(PROCESS_DATES.WHOLE_DATE, 'YYYYMM') AS TRADE_MONTH,
TRADE_FEES.F_PARENT_FIRM_ID AS PARENT_FIRM_ID,
F_BILL_FIRM_ID AS BILL_FIRM_ID,

[marcus_sommer]

The shown error may not be the real error else a follow-up error. Therefore just comment all explicit field-stuff and apply: sql select * from YourDB; I wouldn't be surprised if you see now any sql-error like table/data-base not available.

[ryanfoster84 · Accepted solution]

Turns out the DBA team has been doing alot of changes which was causing this error