Re: Error: Field not found - <CUSTOMERCODE> - Qlik Community

vireshkolagimat · ‎2019-02-28

Hi All,

I've scheduled task which extracts the data from database and saves it in qvd format for every two hours (incremental reload).

The problem is every now and then i am getting the error as field not found but actually the field is available in the script.

Please find the attached log file for the reference.

Thanks for the support.

Regards,

Viresh

28/02/2019 8:48:30 AM: Execution started.
28/02/2019 8:48:30 AM: QlikView Version:11.20.13507.0
28/02/2019 8:48:30 AM: CPU Target x64
28/02/2019 8:48:30 AM: Operating System Windows 7 Professional Service Pack 1 (64 bit edition)
28/02/2019 8:48:30 AM: Wow64 mode Not using Wow64
28/02/2019 8:48:30 AM: MDAC Version 6.1.7601.17514
28/02/2019 8:48:30 AM: MDAC Full Install Version 6.1.7601.17514
28/02/2019 8:48:30 AM: PreferredCompression 2
28/02/2019 8:48:30 AM: EnableParallelReload 1
28/02/2019 8:48:30 AM: ParallelizeQvdLoads 1
28/02/2019 8:48:30 AM: AutoSaveAfterReload 0
28/02/2019 8:48:30 AM: BackupBeforeReload 0
28/02/2019 8:48:30 AM: EnableFlushLog 0
28/02/2019 8:48:30 AM: SaveInfoWhenSavingFile 0
28/02/2019 8:48:30 AM: UserLogfileCharset 1200
28/02/2019 8:48:30 AM: OdbcLoginTimeout -1
28/02/2019 8:48:30 AM: OdbcConnectionTimeout -1
28/02/2019 8:48:30 AM: ScriptWantsDbWrite false
28/02/2019 8:48:30 AM: ScriptWantsExe false
28/02/2019 8:48:30 AM: LogFile CodePage Used: 1200
28/02/2019 8:48:30 AM: Reload Executed By MDCOMAN\MDCIT
28/02/2019 8:48:30 AM: Process Executing: QVB
28/02/2019 8:48:30 AM: Process ID: 109280
28/02/2019 8:48:30 AM: 0002 SET ThousandSep=','
28/02/2019 8:48:30 AM: 0003 SET DecimalSep='.'
28/02/2019 8:48:30 AM: 0004 SET MoneyThousandSep=','
28/02/2019 8:48:30 AM: 0005 SET MoneyDecimalSep='.'
28/02/2019 8:48:30 AM: 0006 SET MoneyFormat='$#,##0.00;($#,##0.00)'
28/02/2019 8:48:30 AM: 0007 SET TimeFormat='h:mm:ss TT'
28/02/2019 8:48:30 AM: 0008 SET DateFormat='DD/MM/YYYY'
28/02/2019 8:48:30 AM: 0009 SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT'
28/02/2019 8:48:30 AM: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
28/02/2019 8:48:30 AM: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
28/02/2019 8:48:30 AM: 0013 ODBC CONNECT*XUserId*XPassword*
28/02/2019 8:48:31 AM: 0015 sql alter session set nls_language = 'AMERICAN'
28/02/2019 8:48:31 AM: 0019 set _qvd_path = '..\Data\Qvds\Raw_Qvds\'
28/02/2019 8:48:31 AM: 0020 set _qvd_name = 'SalesHistory'
28/02/2019 8:48:31 AM: 0021 set vKeyField = ORDER_LINE_ID
28/02/2019 8:48:31 AM: 0022 set vUpdateField = INV_LAST_UPDATE_DATE
28/02/2019 8:48:31 AM: 0026 let vFileLen = FileSize('..\Data\Qvds\Raw_Qvds\SalesHistory.qvd')
28/02/2019 8:48:31 AM: 0028 trace '>> QVD Size: '11889834
28/02/2019 8:48:31 AM: 0028 '>> QVD Size: '11889834

28/02/2019 8:48:31 AM: 0032 if len('11889834') = 0 then
28/02/2019 8:48:31 AM: 0137 TRACE 'Incremental reload begins...'
28/02/2019 8:48:31 AM: 0137 'Incremental reload begins...'

28/02/2019 8:48:31 AM: 0141 set vKeyQVD = 'OrdersMaxKey_history'
28/02/2019 8:48:31 AM: 0143 OrdersMaxKey_history:
28/02/2019 8:48:31 AM: 0144 load
28/02/2019 8:48:31 AM: 0145 date(MaxKey) as MaxKey
28/02/2019 8:48:31 AM: 0146 from
28/02/2019 8:48:31 AM: 0147 ..\Data\Qvds\Raw_Qvds\OrdersMaxKey_history.qvd(qvd)
28/02/2019 8:48:31 AM: 1 fields found: MaxKey, 1 lines fetched
28/02/2019 8:48:31 AM: 0149 let vMaxKey = Peek('MaxKey')
28/02/2019 8:48:31 AM: 0151 trace '>>> Last Max Key value stored: ' & 28/02/2019
28/02/2019 8:48:31 AM: 0151 '>>> Last Max Key value stored: ' & 28/02/2019

28/02/2019 8:48:31 AM: 0153 drop table OrdersMaxKey_history
28/02/2019 8:48:31 AM: 0157 SalesHistory:
28/02/2019 8:48:31 AM: 0158 LOAD
28/02/2019 8:48:31 AM: 0159 CUSTOMERCODE as CustomerCode,
28/02/2019 8:48:31 AM: 0160 WAREHOUSECODE as WarehouseCode,
28/02/2019 8:48:31 AM: 0161 PRODUCTCODE as ProductCode,
28/02/2019 8:48:31 AM: 0162 INVOICENO as InvoiceNo,
28/02/2019 8:48:31 AM: 0163 INVOICEDATE as InvoiceDate,
28/02/2019 8:48:31 AM: 0164 SALESQTY as SalesQty,
28/02/2019 8:48:31 AM: 0165 SALESVALUE as SalesValue,
28/02/2019 8:48:31 AM: 0166 SALESCOST as SalesCost,
28/02/2019 8:48:31 AM: 0167 SALESLISTVALUE as SalesListValue,
28/02/2019 8:48:31 AM: 0168 SALESCOMMISSION as SalesCommission,
28/02/2019 8:48:31 AM: 0169 SALESCUSTOMERGROUP as SalesCustomerGroup,
28/02/2019 8:48:31 AM: 0170 SALESTERRITORY as SalesTerritory,
28/02/2019 8:48:31 AM: 0171 REGIONCODE as RegionCode,
28/02/2019 8:48:31 AM: 0172 CHANNELCODE as ChannelCode,
28/02/2019 8:48:31 AM: 0173 SALESMANCODE as SalesmanCode,
28/02/2019 8:48:31 AM: 0174 ROUTECODE as RouteCode,
28/02/2019 8:48:31 AM: 0175 SALESUNIT as SalesUnit,
28/02/2019 8:48:31 AM: 0176 SALESUNITFACTOR as SalesUnitFactor,
28/02/2019 8:48:31 AM: 0177 RETURNTYPE as ReturnType,
28/02/2019 8:48:31 AM: 0178 ORDER_TYPE,
28/02/2019 8:48:31 AM: 0179 ORDER_LINE_ID,
28/02/2019 8:48:31 AM: 0180 ORDER_NUMBER,
28/02/2019 8:48:31 AM: 0181 ORDEREDDATE as OrderedDate,
28/02/2019 8:48:31 AM: 0182 floor(INV_LAST_UPDATE_DATE) as INV_LAST_UPDATE_DATE
28/02/2019 8:48:31 AM: 0183 SQL
28/02/2019 8:48:31 AM: 0184 SELECT
28/02/2019 8:48:31 AM: 0185 CUSTOMERCODE,
28/02/2019 8:48:31 AM: 0186 WAREHOUSECODE,
28/02/2019 8:48:31 AM: 0187 WAREHOUSENAME,
28/02/2019 8:48:31 AM: 0188 ORDER_NUMBER,
28/02/2019 8:48:31 AM: 0189 PRODUCTCODE,
28/02/2019 8:48:31 AM: 0190 INVOICENO,
28/02/2019 8:48:31 AM: 0191 INVOICEDATE,
28/02/2019 8:48:31 AM: 0192 SALESCOST,
28/02/2019 8:48:31 AM: 0193 SALESLISTVALUE,
28/02/2019 8:48:31 AM: 0194 SALESCOMMISSION,
28/02/2019 8:48:31 AM: 0195 SALESCUSTOMERGROUP,
28/02/2019 8:48:31 AM: 0196 SALESTERRITORY,
28/02/2019 8:48:31 AM: 0197 REGIONCODE,
28/02/2019 8:48:31 AM: 0198 CHANNELCODE,
28/02/2019 8:48:31 AM: 0199 SALESMANCODE,
28/02/2019 8:48:31 AM: 0200 ROUTECODE,
28/02/2019 8:48:31 AM: 0201 SALESUNIT,
28/02/2019 8:48:31 AM: 0202 SALESUNITFACTOR,
28/02/2019 8:48:31 AM: 0203 RETURNTYPE,
28/02/2019 8:48:31 AM: 0204 SALESQTY,
28/02/2019 8:48:31 AM: 0205 SALESVALUE,
28/02/2019 8:48:31 AM: 0206 ORDER_TYPE,
28/02/2019 8:48:31 AM: 0207 ORDER_LINE_ID,
28/02/2019 8:48:31 AM: 0208 ORDEREDDATE,
28/02/2019 8:48:31 AM: 0209 INV_LAST_UPDATE_DATE
28/02/2019 8:48:31 AM: 0210 FROM APPS.XXEGC_QV_SALES_HISTORY_V
28/02/2019 8:48:31 AM: 0211 where DIVISION_CODE='CA MDC OU' and INV_LAST_UPDATE_DATE >= trunc(to_date('28/02/2019','DD/MM/YYYY'))
28/02/2019 9:03:33 AM: Error: Field not found - <CUSTOMERCODE>
28/02/2019 9:03:33 AM: General Script Error
28/02/2019 9:03:33 AM: Execution Failed
28/02/2019 9:03:33 AM: Execution finished.

crusader_ · ‎2019-02-28

Hi,

When then error occurs, check if CUSTOMER_CODE field is accessible in APPS.XXEGC_QV_SALES_HISTORY_V table.

If this script runs every 2 hours and error occurs only from time to time, then you need to look into DB side not Qlik.

Hope this helps.

//Andrei

VishalWaghole · ‎2019-02-28

Check at database side whether field is available or not in respective table.

Regards,
Vishal Waghole

vireshkolagimat · ‎2019-02-28

Hi, The filed is very much available in the database and if i do a full reload, it just works fine but getting error while doing the incremental load.

Thanks.

vireshkolagimat · ‎2019-02-28

Hi, I've scheduled few other tasks which is based on the same view and it just runs fine.

If it was DB side issue, other tasks should also fail which uses the history view. Am i right?

Regards,

Viresh

VishalWaghole · ‎2019-02-28

If it's issue at qlik site, then debug your extractor script and check historical qvd if field is available or not in qvd.

vireshkolagimat · ‎2019-02-28

Hi, i tried to execute in debug mode but not getting any error.

crusader_ · ‎2019-02-28

You should focus on WHEN the error happens rather than field, table, incremental/non-incremental... all above doesn't matter, if your script works fine at least once... (unless someone keep changing the view)

Look at the timestamps:

28/02/2019 8:48:31 AM: 0210 FROM APPS.XXEGC_QV_SALES_HISTORY_V
28/02/2019 8:48:31 AM: 0211 where DIVISION_CODE='CA MDC OU' and INV_LAST_UPDATE_DATE >= trunc(to_date('28/02/2019','DD/MM/YYYY'))
28/02/2019 9:03:33 AM: Error: Field not found - <CUSTOMERCODE>

Your script is trying to do something for 15 minutes and than fails... and it fails as per your previous reply in a certain time.

Check what's happening on DB side at that time, also you can try to enable "Tracing Log" of your ODBC driver to catch exact error code.

And again, if your script works fine in "full" mode and "incremental" mode when you're running it manually - the problem is NOT in Qlik.

Hope this helps.

//Andrei

vireshkolagimat · ‎2019-03-01

Hi Andrei,

Thanks for all the suggestions.

I am looking at the task history in QMC and it is just running fine from yesterday evening.

I will monitor the tasks once the user start booking the orders in the system.

How to enable the trace log file and what information should i capture. Will you please explain.

In my current script, i already have trace command something like TRACE >>>> No of records.

let me know if this is what you are suggesting.

Regards,

Viresh

Annie4 · ‎2024-03-20

Hi @vireshkolagimat

Were you able to resolve this & how ?

We are facing the same issue .

Thanks !