Max Date

[user8]

Hi,

I have an incremental load script:

SRO:
LOAD
[Form Ref No],
[Form Name],
CompletionDate
FROM [lib://GS_Folder/SRO.qvd] (qvd);

// Find last modified date
Last_Updated_Date:
load max (CompletionDate) as Maxdate
resident SRO;

// Store last modified date to a variable
Let Last_Updated_Date=peek('Maxdate',0,'Last_Updated_Date');

The CompletionDate data is in the format of 21/11/2019 5:06:29 PM.
Will the "load max (CompletionDate) as Maxdate" command work here? 

I'm asking as it does not seems to identify the CompletionDate and the incremental data are not being pulled from DB to the QVD file.

Appreciate any help.

[vunguyenq89]

Hi,

You should check field CompletionDate in Data Model Viewer to see if it contains tags $numeric $timestamp.

If the field has tags $ascii $text instead, you should convert it to date type. For example

SRO:
LOAD
[Form Ref No],
[Form Name],
Date(Date#(CompletionDate,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as CompletionDate
FROM [lib://GS_Folder/SRO.qvd] (qvd);

Hope this helps.

BR,

Vu Nguyen

[user8]

Thank you Vu Nguyen. I was able to modify Date# as below:

// Loading data from QVD
SRO:
LOAD 
	[Form Ref No],
	[Form Name],
        Date(Date#(CompletionDate,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as CompletionDate
FROM [lib://GS_Folder/SRO.qvd] (qvd);

// Find last modified date
Last_Updated_Date:
load max (CompletionDate) as Maxdate
resident SRO;

// Store last modified date to a variable
Let Last_Updated_Date=peek('Maxdate',0,'Last_Updated_Date');

When I try to add it to below scripts it says "'Date#' is not a recognized built-in function name".
Can you advise how should I modify the "CompletionDate" for the below scripts:

// Load incremental data
Incremental:
LOAD 
[Form Ref No],
[Form Name],
CompletionDate;

SELECT
"Form Ref No",
"Form Name", 
CompletionDate 
FROM TABLE where CompletionDate > $(Last_Updated_Date);

// Concatenate with QVD
Concatenate
LOAD 
[Form Ref No],
[Form Name],
CompletionDate
FROM [lib://GS_Folder/SRO.qvd] (qvd);

// Replace old QVD file
Store Incremental into [lib://GS_Folder/SRO.qvd] (qvd);

// Drop Incremental Table
Drop table Incremental;

[vunguyenq89]

"'Date#' is not a recognized built-in function name" sounds like an error message in SQL Server rather than Qlik Sense. Did you use Date#() function in a SQL statement?

[user8]

I'm using MS SQL DB. The CompletionDate column format is 21/11/2019 5:06:29 PM. I think Maxdate will not recognise this format hence I'm converting it to 21/11/2019 using below script:

LOAD
.....
Date(Date#(CompletionDate,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as CompletionDate;

.....

SELECT
.....
FORMAT (getdate(), 'dd/MM/yyyy') as CompletionDate
FROM Table where CompletionDate > $(Last_Updated_Date);

When I run it, it says

The following error occurred:
Connector reply error: SQL##f - SqlState: 42000, ErrorCode: 102, ErrorMsg: Incorrect syntax near '>'.,

I suspect it cannot read $(Last_Updated_Date) as when I change to "where CompletionDate > 21/11/2019;" it was able to run fine.

Any idea?

Below is the full script:

// Loading data from QVD
SRO:
LOAD 
[Form Ref No],
[Form Name],
Date(Date#(CompletionDate,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as CompletionDate
FROM [lib://SRO.qvd] (qvd);

// Find last modified date
Last_Updated_Date:
load max (CompletionDate) as Maxdate
resident SRO;

// Store last modified date to a variable
Let Last_Updated_Date=peek('Maxdate',0,'Last_Updated_Date');

// Delete Table SRO
Drop table SRO;

LIB CONNECT TO 'SQL_SRO_ODBC';

// Load incremental data
Incremental:
LOAD 
[Form Ref No],
[Form Name],
Date(Date#(CompletionDate,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as CompletionDate;

SELECT
"Form Ref No",
"Form Name", 
FORMAT (getdate(), 'dd/MM/yyyy') as CompletionDate 
FROM Table 
where CompletionDate > $(Last_Updated_Date);

[vunguyenq89]

Hi,

I can see a couple of things to check here:

1. Comment out "Drop table SRO;". Reload, open Data Model Viewer, see field CompletionDate is tagged with $numeric $timestamp
2. Also after reload, open variable viewer and see which value variable Last_Updated_Date holds. It may happen that this variable stores a numeric value (for example 43470) instead of date format as an effect of Max() function you use to find Maxdate. If this is the case, convert it back to date format:

Let Last_Updated_Date=Date(peek('Maxdate',0,'Last_Updated_Date'));

Hope this helps,

BR,

Vu Nguyen

[user8]

Thanks Vu Nguyen for your help.
The command "Let Last_Updated_Date=Date(peek('Maxdate',0,'Last_Updated_Date'));" helped to enable CompletionDate to be recognized as a valid Date.

I ran the incremental load and here are the results (refer to my comments in red) :

Started loading data
SRO << SRO
(QVD (row-based) optimized)
Lines fetched: 1,181                           ====> There were 1,181 lines in the existing QVD file
Last_Updated_Date << SRO
Lines fetched: 1
>>> 12/9/2019                                       ====> Maxdate
Incremental << vwSROHeader
Lines fetched: 1,189                           ====> There were 1,189 lines in DB, which means 8 new lines will be added to the QVD file
Incremental << SRO
(QVD (row-based) optimized)
Lines fetched: 1,189                           ====> My question is here it shows 1,189 lines fetched. Does this mean it fetched all the 1,189 lines again or just the 8 new lines to the QVD File?
Creating search index
Search index creation completed successfully
App saved
Finished successfully