Field Not Found Error

[Demlet]

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);

[mikaelsc · Accepted solution]

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

[Demlet]

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