topic Re: Field Not found - but not asked for... in QlikView

Field Not found - but not asked for...

Thu, 06 Mar 2014 16:19:59 GMT

Hi Guys,

I'm getting this error when loading from a resident table and i cannot figure out why. <ID> is not a field i've used or stated that i require.

Error is as follows;

Field not found - <ID>

SaveFile:

Load InboundCallTrackingTblID,

CallID,

CalledNumber,

CallerNumber,

AgentID,

Action,

Answered,

AnsweredNoVoicemail,

Missed,

InternalOverFlowTimeOut,

InternalOverFlowToLongWaiting,

InternalOverFlowToManyWaiting,

InternalOverFlowCalendar,

InternalOverFlowHoliday,

InternalOverFlowManual,

InternalOverFlowNoAgent,

ExternalOverFlowTimeOut,

ExternalOverFlowToLongWaiting,

ExternalOverFlowToManyWaiting,

ExternalOverFlowCalendar,

ExternalOverFlowHoliday,

ExternalOverFlowManual,

ExternalOverFlowNoAgent,

StartTime,

CallDate,

CallHour,

CallMinute,

CallTime,

CallTableDate,

MinutesThirty,

MinutesFifteen,

EndTime,

Duration,

WaitDuration,

HoldTimeDuration,

SkillID,

CallerTitle,

CallerName,

CallerSureName,

CallerCompany,

BrandExAdmin,

CallerStreet,

CallerZIP,

CallerCity,

CallerCountry,

CallerPhone,

VIPClassID,

PhoneClassID,

connected,

TransferID1,

TransferID2,

TransferID3,

TransferID4,

PhoneCallStart,

Classification,

PickAgentName,

PickMode

Resident Inbound

Where Date(CallTableDate) < '06/03/2014'

I'm not referencing a field called <ID> so where is it getting this from? I've also tried replacing the list with

LOAD *

but, again, it says it cannot find the field <ID>.

Anyone got any ideas?

Regards,

Nick

Re: Field Not found - but not asked for...

Anonymous — Thu, 06 Mar 2014 16:21:57 GMT

Nicholas

Sometime strange error messages like this are caused by the statement above or below.

Could you give the whole script chunk, including above & below script.

Best Regards, Bill

Re: Field Not found - but not asked for...

fkeuroglian — Thu, 06 Mar 2014 16:26:34 GMT

Hi, it is the only load table that you have in your script?

Re: Field Not found - but not asked for...

Thu, 06 Mar 2014 16:32:01 GMT

Hi Bill,

Code is;

Page1:

/* Setup for incremental load. */

The following items are used multiple times in the script.

It's easiest to specify them in variables.

Let vPath = '\\Qlikview01\Qlikview\';

Let vToday = Date(Today(2));

SET vQvdFile ='$(vPath)Data\QVDs\MCC\MCC-Inbound-TR.qvd'; //?? The QVD filename

SET vTableName='Inbound'; //?? The name of the QV table we are loading on the Data Load tab

SET vPK='InboundCallTrackingTblID'; //?? Data Primary Key

Update the QVD with changes.

Page2:

// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False

LET vQvdExists = if(Filesize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN // QVD exists, we will do an incremental reload

Inbound:

LOAD InboundCallTrackingTblID,

CallID,

CalledNumber,

CallerNumber,

AgentID,

Action,

Answered,

AnsweredNoVoicemail,

Missed,

InternalOverFlowTimeOut,

InternalOverFlowToLongWaiting,

InternalOverFlowToManyWaiting,

InternalOverFlowCalendar,

InternalOverFlowHoliday,

InternalOverFlowManual,

InternalOverFlowNoAgent,

ExternalOverFlowTimeOut,

ExternalOverFlowToLongWaiting,

ExternalOverFlowToManyWaiting,

ExternalOverFlowCalendar,

ExternalOverFlowHoliday,

ExternalOverFlowManual,

ExternalOverFlowNoAgent,

StartTime,

Date(CallDate) as CallDate,

CallHour,

CallMinute,

CallTime,

CallTableDate,

MinutesThirty,

MinutesFifteen,

EndTime,

Duration,

WaitDuration,

HoldTimeDuration,

SkillID,

CallerTitle,

CallerName,

CallerSureName,

CallerCompany,

BrandExAdmin,

CallerStreet,

CallerZIP,

CallerCity,

CallerCountry,

CallerPhone,

VIPClassID,

PhoneClassID,

connected,

TransferID1,

TransferID2,

TransferID3,

TransferID4,

PhoneCallStart,

Classification,

PickAgentName,

PickMode,

'Old' as Check

FROM

$(vQvdFile)

(qvd)

Where StartTime > Today(2)-730

;

// Get the max date from this QVD for use in incremental SELECT

LOAD date(max(StartTime),'YYYY-MM-DD hh:mm:ss[.fff]') as maxdate Resident Inbound;

//?? Set the name of the date or datetime field

//?? Date may need to be formatted to match the format expected by your SQL SELECT or LOAD statement.

//?? e.g., date(peek('maxdate'),'MMDDYYYY');

LET vIncrementalExpression ='WHERE StartTime >= ' & chr(39) & peek('maxdate') & chr(39);

Drop Field maxdate;

ELSE // QVD does not exist

LET vIncrementalExpression = ''; // No QVD. Force full reload

END IF

Page 3:

LOAD

ID AS InboundCallTrackingTblID,

CallID,

CalledNumber,

CallerNumber,

AgentID,

Action,

//Wait Time Variables

If(Action = 'Connected' OR Action = 'Voicemail' OR Action = 'TransferExternal' OR Action = 'TransferInternal',1,0) AS Answered,

If(Action = 'Connected' OR Action = 'TransferExternal' OR Action = 'TransferInternal',1,0) AS AnsweredNoVoicemail,

If(Action <> 'Connected' AND Action <> 'TransferExternal' AND Action <> 'Voicemail' AND Action <> 'TransferInternal',1,0) AS Missed,

//Internal Overflow Variables

If(Action = 'TransferInternal' OR Action = 'TimeOut',1,0) AS InternalOverFlowTimeOut,

If(Action = 'TransferInternal' OR Action = 'ToLongWaiting',1,0) AS InternalOverFlowToLongWaiting,

If(Action = 'TransferInternal' OR Action = 'ToManyWaiting',1,0) AS InternalOverFlowToManyWaiting,

If(Action = 'TransferInternal' OR Action = 'CalendarClosed',1,0) AS InternalOverFlowCalendar,

If(Action = 'TransferInternal' OR Action = 'HolidayClosed',1,0) AS InternalOverFlowHoliday,

If(Action = 'TransferInternal' OR Action = 'ManualClosed',1,0) AS InternalOverFlowManual,

If(Action = 'TransferInternal' OR Action = 'NoAgent',1,0) AS InternalOverFlowNoAgent,

//External Overflow Variables

If(Action = 'TransferExternal' OR Action = 'TimeOut',1,0) AS ExternalOverFlowTimeOut,

If(Action = 'TransferExternal' OR Action = 'ToLongWaiting',1,0) AS ExternalOverFlowToLongWaiting,

If(Action = 'TransferExternal' OR Action = 'ToManyWaiting',1,0) AS ExternalOverFlowToManyWaiting,

If(Action = 'TransferExternal' OR Action = 'CalendarClosed',1,0) AS ExternalOverFlowCalendar,

If(Action = 'TransferExternal' OR Action = 'HolidayClosed',1,0) AS ExternalOverFlowHoliday,

If(Action = 'TransferExternal' OR Action = 'ManualClosed',1,0) AS ExternalOverFlowManual,

If(Action = 'TransferExternal' OR Action = 'NoAgent',1,0) AS ExternalOverFlowNoAgent,

StartTime,

Floor(StartTime) AS CallDate,

Hour(StartTime) AS CallHour,

Minute(StartTime) AS CallMinute,

Time(StartTime) AS CallTime,

Date(StartTime, 'DD/MM/YYYY') AS CallTableDate,

//Interval Setup

if ( minute(StartTime) < 30, '00', '30') AS MinutesThirty,

if ( minute(StartTime) < 15, '00', if( minute(StartTime) < 30, '15', if( minute(StartTime) < 45, '30', '45' ))) AS MinutesFifteen,

EndTime,

Duration,

//Wait Durration Variable

if(Action='Waiting',Duration,Null()) AS WaitDuration,

//Hold Time Duration Variable

if(Action='OnHold',Duration,Null()) AS HoldTimeDuration,

SkillID,

CallerTitle,

CallerName,

CallerSureName,

CallerCompany,

// If(CallerCompany='Fortoak' OR CallerCompany='Yes' OR CallerCompany='Church Supplies' OR PhoneClass='7' OR PhoneClass='8' OR PhoneClass='9' OR PhoneClass='10' OR PhoneClass='11' OR PhoneClass='12', 1, 0) AS BrandExAdmin,

If(PhoneClass='7' OR PhoneClass='8' OR PhoneClass='9' OR PhoneClass='10' OR PhoneClass='11' OR PhoneClass='12' OR PhoneClass='15' OR PhoneClass='16' OR PhoneClass='17' OR PhoneClass='18', 1, 0) AS BrandExAdmin,

CallerStreet,

CallerZIP,

CallerCity,

CallerCountry,

CallerPhone,

VIPClass AS VIPClassID,

PhoneClass AS PhoneClassID,

connected,

TransferID1,

TransferID2,

TransferID3,

TransferID4,

PhoneCallStart,

Classification,

AgentName AS PickAgentName,

PickMode,

'New' as Check

;

SQL SELECT *

FROM CCDB_02.dbo.InboundCallTracking Order by StartTime ASC

$(vIncrementalExpression)

;

Page4:

QUALIFY * ;

SaveFile:

Load *

Resident Inbound

Where Date(CallTableDate) < '$(vToday)'

;

UnQUALIFY * ;

EXIT Script;

Store SaveFile INTO $(vQvdFile);

Drop Table SaveFile;

Exit Script;

END

What I'm trying to do is load everything except the current day's data so that i can archive it off. The SQL server will be offsite so i want the majority of the Data to be held locally in a QVD and then we simply are doing a SQL connect for the latest days data. The Report needs to reload every 30 minutes so i want the external SQL connection to get just today's records.

Hope that makes sense.

Nick

Re: Field Not found - but not asked for...

sudeepkm — Thu, 06 Mar 2014 16:35:46 GMT

Page 3:

LOAD

ID AS InboundCallTrackingTblID,

CallID,

CalledNumber,

CallerNumber,

AgentID,

Action,

you are using ID field on tab 3. Please check if you have it in the source?

Re: Field Not found - but not asked for...

Thu, 06 Mar 2014 16:48:07 GMT

Thanks Sudeep,

Well spotted.

However, if it's being joined to the Inbound table and being renamed on the way in, shouldn't it be stored under InboundCallTrackingID and not ID? It certainly seems like the culprit but I can't understand why that would be...

If i check the table structure after Page 3 loads, i do not have a field called ID available, plus if i'm explicitly stating which fields to load in Page 4 (see first post), why would it be looking for a field called <ID>?

Regards,

Nick

Re: Field Not found - but not asked for...

Thu, 06 Mar 2014 16:53:12 GMT

In Page 3 you are using

ID as ID AS InboundCallTrackingTblID.

Can you check and make sure that it exists in the source table. Do a Ctrl+F in your script and search all tabs and see where all this ID exits and see if its really available in the source file.

Conversely you can also look at the log file and see in which table its failing because of missing field and fix it.

Thanks

Re: Field Not found - but not asked for...

Thu, 06 Mar 2014 16:56:08 GMT

Can you attach your log file?

Re: Field Not found - but not asked for...

Wed, 12 Mar 2014 20:16:42 GMT

I had the same problem - until I found some fields in the previous LOAD statement had gone unmatched. Making sure they got matched resolved this issue.

Re: Field Not found - but not asked for...

stevedark — Wed, 12 Mar 2014 20:49:41 GMT

I'm slightly confused. The code that you posted in your initial question doesn't actually appear in the longer script you posted.

The ID on page three is coming from CCDB_02.dbo.InboundCallTracking, take a look in your database as to whether that table contains an ID field.

I typically ensure that all fields are listed in the SQL statement rather than using a *. This makes the code longer but it does mean you can eyeball the fields between the SQL part and the QlikView part of the statement. Also, if you have a field that does not exist in the SQL part you will get a sensible ODBC/OLEDB error message.

Hope that helps,
Steve

Re: Field Not found - but not asked for...

alec1982 — Wed, 12 Mar 2014 21:13:40 GMT

I suggest you comment part of your script and load them one table at a time to insure which table has the error. most likely the issue in page 3 and the ID field doesn't exist in your database.