Timestamp incremental load

[QlikSenseUser1]

Hi guys, 

I am trying to perform an incremental load routine, where I am trying to define the new data based on data after a certain time. I have a timestamp in the data on which I tried to define a variable "maxdate" which defines the latest date. 

I have two questions  (see below for code) : 

1) My timestamp has the column name "STIME" and is of following format: 181203 00:02 (ie.: YYMMDD hh:mm). Do I have to specify which column in the original data is my timestamp or does qlik understand through: 

SET TimestampFormat='YY.MM.DD hh:mm[.fff]';

2) Trying to implement the incremental load routing I am getting following message: 

"The following error occurred:
Field 'a' not found
The error occurred here:
?" 

debugging stops at line 27 (just below : //5.Pull new rows/delta.)

Data sample is attached, here is my current code:

// 1. Primary Data Load
data1:
LOAD
STIME,
//Date(Date#[STIME],'YYYY-MM-DD hh:mm'), AS STIME,
// Date(Floor([STIME])) as Date,
NE_ID,
OBJID,
CALLSETUPDURATION,
CALLSETUPSUCCESSRATIO
FROM [lib://Incremental Load/ZafacoLoad1.xlsx]
(ooxml, embedded labels, table is Zafaco181203);

//2. Create a qvd file.
store data1 into 'lib://QVD/ZafacoTest.qvd';
drop table data1;

//3. Find maximum date.
max_date:
LOAD
max(STIME) as Maxdate
FROM 'lib://QVD/ZafacoTest.qvd'(qvd);

//4. Store Maximum date in a variable.
Let Maxdate = floor(peek('Maxdate'));

//5.Pull new rows/delta.
data2:
NoConcatenate
LOAD
STIME,
NE_ID,
OBJID,
CALLSETUPDURATION,
CALLSETUPSUCCESSRATIO
FROM [lib://Incremental Load/ZafacoLoad2.xlsx]
(ooxml, embedded labels, table is Zafaco181205)
where STIME> $(Maxdate);

//6 Concatenate.
Concatenate (data2)
LOAD
STIME,
NE_ID,
OBJID,
CALLSETUPDURATION,
CALLSETUPSUCCESSRATIO
FROM [lib://QVD/ZafacoTest.qvd] (qvd);
STORE data2 into [lib://QVD/ZafacoTest.qvd] (qvd);

[QlikSenseUser1]

Hi Dilipranjith,  Jontydkpi, 

The code runs without an error message. But the incremental load is not performed. 
I have adapted the code for troubleshooting by saving each step. It appears the delta load was not performed. 
The mistake appears to be in the variable "maxdate_Zafaco" as its not being generated. 
I am thus coming back to my initial questions: 


1)  Does Qlik recognize my timestamp correctly? (my column with my timestamp is called STIME, I have used Timestamp#(STIME,'YYMMDD hh:mm') as STIME, )
2) Does the max operator work correctly on the timestamp column?

Thanks

[QlikSenseUser1]

Hi Dilipranjith,  Jontydkpi, 

The code runs without an error message. But the incremental load is not performed. 
I have adapted the code for troubleshooting by saving each step. It appears the delta load was not performed. 
The mistake appears to be in the variable "maxdate_Zafaco" as its not being generated. 
I am thus coming back to my initial questions: 


1)  Does Qlik recognize my timestamp correctly? (my column with my timestamp is called STIME, I have used Timestamp#(STIME,'YYMMDD hh:mm') as STIME, )
2) Does the max operator work correctly on the timestamp column?

Thanks. 

Here is the code: 

// 1. Primary Data Load
data1:
LOAD
Timestamp#(STIME,'YYMMDD hh:mm') as STIME,
NE_ID,
OBJID,
CALLSETUPDURATION,
CALLSETUPSUCCESSRATIO
FROM 'lib://Incremental Load/ZafacoLoad1.xlsx'
(ooxml, embedded labels, table is Zafaco181203);

//2. Create a qvd file.
store data1 into 'lib://QVD/ZafacoTest.qvd';
drop table data1;

//3. Find maximum date.
max_date:
LOAD
max(STIME) as Maxdate_Zafaco
FROM 'lib://QVD/ZafacoTest.qvd'(qvd);

//4. Store Maximum date in a variable.
Let Maxdate_Zafaco = floor(peek('Maxdate_Zafaco'));

// Peek() finds the value of a field in a table for a row that has already been loaded
// or that exists in internal memory. The row number can be specified, as can the table.

//5.Pull new rows/delta.
data2:
NoConcatenate
LOAD
//STIME,
Timestamp#(STIME,'YYMMDD hh:mm') as STIME,
NE_ID,
OBJID,
CALLSETUPDURATION,
CALLSETUPSUCCESSRATIO
FROM [lib://Incremental Load/ZafacoLoad2.xlsx]
(ooxml, embedded labels, table is Zafaco181205)
where STIME> $(Maxdate_Zafaco);
STORE data2 into 'lib://QVD/ZafacoTestdelta.qvd' (qvd);

//6 Concatenate.
Concatenate (data2)
LOAD
STIME,
NE_ID,
OBJID,
CALLSETUPDURATION,
CALLSETUPSUCCESSRATIO
FROM 'lib://QVD/ZafacoTest.qvd' (qvd);
STORE data2 into 'lib://QVD/ZafacoTestComplete.qvd' (qvd);

 Thanks

[dplr-rn]

i can only point you in the right direction. unfortunately not possible to debug the entire code.
the error is fixed i suggest you load debug the code properly. load part of the statements to assess each step

e.g. load only step 1 see if the data is correct.
and play around with it

[QlikSenseUser1]

Hi, 

thanks, thats what I did.  I have debugged and narrowed down the problem to following (as stated above): 

The second table data2 is not created correctly. I am suspecting that the Maxdate_Zafaco variable is not properly recognized as (I suspect) my max operator on my timestamp column "STIME" is not performing as it is supposed to. Basically, I am trying to understand why these lines are not working properly: 

data1:
LOAD
Timestamp#(STIME,'YYMMDD hh:mm') as STIME,
FROM....
...
max_date:
LOAD
max(STIME) as Maxdate_Zafaco
FROM...
...
 Let Maxdate_Zafaco = floor(peek('Maxdate_Zafaco'));


I am suspecting that the variable saving the max date (from timestamp) ie. Maxdata_Zafaco is not correctly defined in the routine. 

Thanks for your time

[QlikSenseUser1]

The line that is not working is: 

where STIME> $(Maxdate_Zafaco);

so the max operator on the timestamp column does not perform correctly. If I apply the maxoperator on a column with a consecutive variable it works fine. e.g. OBJID is a continuous variable, applying the maxoperator on OBJID saving it in a variable and implementing the incremental load works fine :

where OBJID> $(Maxdate_Zafaco)


So the question remains:
1) how is a column specified such that the timestamp is correctly recognised
2) how do you apply a max operator on said timestamp

Thanks

[timpoismans]

Most likely, from the current issue you've pinpointed, the [STIME] field and your variable don't have the same format. 
Using the max() function, will return a number, while [STIME] has the timestamp format.

I'd suggest you try and match both formats and then try applying the where clause.