topic Re: Timestamp incremental load in Visualization and Usability

Timestamp incremental load

QlikSenseUser1 — Sat, 16 Nov 2024 21:36:27 GMT

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

Re: Timestamp incremental load

dplr-rn — Mon, 14 Jan 2019 14:06:16 GMT

Your column STIME is being loaded as text. therefore the variable is null.

thats the reason for the error

Re: Timestamp incremental load

QlikSenseUser1 — Mon, 14 Jan 2019 14:09:14 GMT

Thanks Dilipranjith for the quick response.
How do I define STIME as date/timestamp? How do I troubleshoot?

Re: Timestamp incremental load

dplr-rn — Mon, 14 Jan 2019 14:16:26 GMT

Load STIME as timestamp using something like
Timestamp#(STIME,'YYMMDD hh:mm') as STIME,

Re: Timestamp incremental load

QlikSenseUser1 — Mon, 14 Jan 2019 16:46:57 GMT

Thanks Dilipranjith. Unfortunately, it didn't work. I have implemented as advised

data1:
LOAD
STIME,
Timestamp#(STIME,'YYMMDD hh:mm') as STIME,....

I am now however getting following error message:

"The following error occurred:

Field names must be unique within table

Data has not been loaded. Please correct the error and try loading again"

Where am I going wrong? Thanks in advance

Re: Timestamp incremental load

dplr-rn — Mon, 14 Jan 2019 16:48:21 GMT

remove the first one

data1:
LOAD
~~STIME,~~
Timestamp#(STIME,'YYMMDD hh:mm') as STIME,....

Re: Timestamp incremental load

QlikSenseUser1 — Mon, 14 Jan 2019 16:59:24 GMT

Thanks Dilipranjith! This is progress, initial error code resolved. Im getting following error code after implementing as suggested:

"The following error occurred:

Field 'a' not found

The error occurred here:

?"

Debugging stopped script at line 25 (after" 5.Pull new rows"

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

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

// // Comments
// // max(timestamp(Date, 'YYYY/MM/DD hh:mm.ff')) as Maxdate

Re: Timestamp incremental load

dplr-rn — Mon, 14 Jan 2019 17:01:59 GMT

isnt that the same error?
debug and check value of variable Maxdate

Re: Timestamp incremental load

dplr-rn — Mon, 14 Jan 2019 17:09:35 GMT

use timestamp# not timestamp
date#/timestamp# function tell qlik a text is a date/timestamp field.
date/timestamp functions gives a formatted output

Re: Timestamp incremental load

dplr-rn — Mon, 14 Jan 2019 19:38:56 GMT

Did it work?
If so mark as answered and close the thread

Re: Timestamp incremental load

QlikSenseUser1 — Tue, 15 Jan 2019 14:02:16 GMT

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

Re: Timestamp incremental load

QlikSenseUser1 — Tue, 15 Jan 2019 14:03:38 GMT

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

Re: Timestamp incremental load

dplr-rn — Tue, 15 Jan 2019 14:07:25 GMT

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

Re: Timestamp incremental load

QlikSenseUser1 — Tue, 15 Jan 2019 14:40:49 GMT

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

Re: Timestamp incremental load

QlikSenseUser1 — Tue, 15 Jan 2019 15:22:58 GMT

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

Re: Timestamp incremental load

timpoismans — Fri, 18 Jan 2019 09:33:45 GMT

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.