Field Not Found Error

Demlet — Tue, 26 Jun 2018 19:33:48 GMT

Hello Qlik Community,

I have been using incremental load scripts that work perfectly for other DB and REST data. I am however running into a problem trying to get this script to run.

Below is a screenshot of my error that occurs at the [Incremental]: table. Below is also my script. I think the error has to do with my variable Last_Created_Date; commenting out the Where clause stops the error. I tried formatting the DateTime field with date(DateTime) As DateTime, before creating the variable, but experienced the same error.

Any feedback, thoughts, or solutions are greatly appreciated.

-Derek

Error:

//SCRIPT

//Loading Data from QVD

MyTable:

LOAD

date(DateTime) AS DateTime,

"Series 1",

Name

FROM [lib://QVDFiles/My_qvd.qvd] (qvd);

// Find Last Created Date

Last_Created_Date:

LOAD MAX(DateTime) AS MaxDate

RESIDENT MyTable;

//Store Last Created Date to Variable

LET Last_Created_Date = peek('MaxDate',0,'Last_Created_Date');

// Drop Table

Drop Table MyTable;

//INCREMENTAL LOAD

LIB CONNECT TO 'Rest Connection';

RestConnectorMasterTable:

SQL

SELECT

"queryId",

"queryName",

"Name",

"__KEY_root",

(SELECT

"@Value",

"__FK_filters"

FROM "filters" FK "__FK_filters" ArrayValueAlias "@Value"),

(SELECT

"value",

"timestamp",

"__FK_data"

FROM "data" FK "__FK_data")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(Url "MYURL");

[filters]:

LOAD

[@Value] AS [@Value],

[__FK_filters] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_filters]);

Incremental_temp:

Unqualify*;

LOAD

[value] AS "Series 1",

date([timestamp]) AS DateTime,

[__FK_data] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_data]);

JOIN (Incremental_temp)

LOAD

// [queryId] AS [queryId],

// [queryName] AS [queryName],

[Name] AS Name,

[__KEY_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

Incremental:

LOAD

DateTime,

"Series 1",

Name

RESIDENT Incremental_temp

WHERE DateTime > $(Last_Created_Date);

DROP TABLE Incremental_temp;

DROP TABLE [filters];

DROP TABLE RestConnectorMasterTable;

//Concetenate Incremental with QVD

Concatenate

LOAD

DateTime,

"Series 1",

Name

FROM [lib://QVDFiles/My_qvd.qvd] (qvd);

//*** No Unique ID to update with ***/// -- Where not Exists(Unqique_ID);

//Replace Old QVD File

STORE [Incremental] INTO [lib://QVDFiles/My_qvd.qvd] (qvd);

//Drop Incremental Table

Drop Table [Incremental];

Drop Table Last_Created_Date;

//LOAD UPDATED QVD

MyTable:

LOAD

DATE(FLOOR(DateTime)) AS DateTime,

"Series 1" AS Series,

Name AS Name

FROM [lib://QVDFiles/My_qvd.qvd] (qvd);

Re: Field Not Found Error

mikaelsc — Tue, 26 Jun 2018 19:46:50 GMT

check every step of your script... (exit script + debug mode)

1st step: does your date get out as a date

2nd step: does your max gets out correctly

3rd: does the variable get a value

etc

Re: Field Not Found Error

Demlet — Tue, 26 Jun 2018 20:38:25 GMT

Thanks Mikael,

Just FYI to anyone else who is reading:

The Max Date was not getting set correctly. I formatted the date in a load script and overwrote my QVD. The max is now getting set, along with the variable. The field looked as though it was in Date format, but it was not.

Best regards,

-Derek

topic Re: Field Not Found Error in App Development

Field Not Found Error

Re: Field Not Found Error

Re: Field Not Found Error