topic Re: Max Date in App Development

Max Date

user8 — Sat, 16 Nov 2024 19:35:17 GMT

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.

Re: Max Date

vunguyenq89 — Wed, 27 Nov 2019 10:17:31 GMT

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

Re: Max Date

user8 — Thu, 28 Nov 2019 09:50:12 GMT

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;

Re: Max Date

vunguyenq89 — Fri, 29 Nov 2019 08:53:41 GMT

"'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?

Re: Max Date

user8 — Wed, 04 Dec 2019 06:27:47 GMT

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

Re: Max Date

vunguyenq89 — Wed, 04 Dec 2019 07:54:48 GMT

Hi,

I can see a couple of things to check here:

Comment out "Drop table SRO;". Reload, open Data Model Viewer, see field CompletionDate is tagged with $numeric $timestamp
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

Re: Max Date

user8 — Tue, 10 Dec 2019 07:15:31 GMT

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