topic Re: Extract Script Refresh Error - Field Not Found in QlikView

Extract Script Refresh Error - Field Not Found

ryanfoster84 — Thu, 27 Feb 2025 09:00:43 GMT

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

Re: Extract Script Refresh Error - Field Not Found

PrashantSangle — Thu, 27 Feb 2025 09:29:26 GMT

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

Re: Extract Script Refresh Error - Field Not Found

ryanfoster84 — Thu, 27 Feb 2025 09:32:49 GMT

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

Re: Extract Script Refresh Error - Field Not Found

ryanfoster84 — Thu, 27 Feb 2025 09:34:49 GMT

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,

Re: Extract Script Refresh Error - Field Not Found

marcus_sommer — Thu, 27 Feb 2025 12:01:54 GMT

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.

Re: Extract Script Refresh Error - Field Not Found

ryanfoster84 — Sun, 02 Mar 2025 06:35:02 GMT

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