topic Incremental Load for Qlik Sense Entreprise on Windows Server 2016 in App Development

Incremental Load for Qlik Sense Entreprise on Windows Server 2016

TheKiwi — Fri, 08 Jul 2022 15:14:11 GMT

Hi,

I'm trying to implement an Incremental Load in one of my company's APP. But the documentation is not clear about how to do it, especially on Windows.

I've found a Script for Incremental Load on this page :
https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-files-incremental-load.htm

But the script doesn't work and Qlik Sense won't let me use a Windows Path ( Even with Directory[])

My VM is on Windows Server 2016, the DataBase I get the Data from is in MariaDB.

Here is my script :

LIB CONNECT TO 'GLPI (nehs-digital_arthur.riviere)';

Let ThisExecTime = Now( );

QV_Table :

SQL SELECT id, name, date_mod FROM glpi_tickets

WHERE date_mod >= #$(LastExecTime)#

AND date_mod < #$(ThisExecTime)#;

Concatenate LOAD id, name, date_mod FROM Test.QVD

WHERE NOT EXISTS(id);

Inner Join SQL SELECT id FROM glpi_tickets;

If ScriptErrorCount = 0 then

STORE QV_Table INTO Test.QVD;

Let LastExecTime = ThisExecTime;

End If

And i get this error message :
ERROR [42000] [Qlik][MySQL] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND date_mod < #08/07/2022 17:12:19#' at line 5

Have a good day !

Re: Incremental Load for Qlik Sense Entreprise on Windows Server 2016

rubenmarin — Sat, 09 Jul 2022 09:59:11 GMT

Hi, you need to make the query as a mariadb query, I'm not sure but it could be something like:

WHERE date_mod BETWEEN '$(LastExecTime)' AND '$(ThisExecTime)';

You can try first to make the query on MariaDB and then use the varibels to insert the dynamic values to the query

Re: Incremental Load for Qlik Sense Entreprise on Windows Server 2016

TheKiwi — Mon, 11 Jul 2022 08:33:56 GMT

Hi,

Thank you, it solved part of my issue but the query still doesn't work.

On the next line I wrote :

Concatenate LOAD id, name, date_mod FROM Test.QVD (qvd)

WHERE EXISTS(id);

The error message says :
No qualified path for file: ***

But i can't write a windows path in Qlik Sense or it gives me an error :
This statement only works with lib:// paths in this script mode

Re: Incremental Load for Qlik Sense Entreprise on Windows Server 2016

rubenmarin — Mon, 11 Jul 2022 08:38:14 GMT

Hi, you eed to use lib:// to connect, the common procedure is to create a new folder connection and use that connection in lib://format. to have a format example, once you have the connections created just open any file on that connetion so you can see the script.

It would be something like lib://Connection_name\File.ext

Re: Incremental Load for Qlik Sense Entreprise on Windows Server 2016

TheKiwi — Mon, 11 Jul 2022 09:48:49 GMT

Hi, I created a content library and a data connection to it named "FileQVD". QlikSense now gives me a different kind of error.

My query :

Concatenate LOAD id, name, date_mod FROM [lib://FileQVD\Test.qvd] (QVD)

WHERE NOT EXISTS(id);

The error message :
Cannot open file: 'lib://FileQVD\Test.qvd' (Native Path: *** System error: ***)

I don't see any security rule that my block this connection and the folder is not open.

Re: Incremental Load for Qlik Sense Entreprise on Windows Server 2016

rubenmarin — Mon, 11 Jul 2022 10:26:28 GMT

Hi, to open the qvd, the qvd needs to exists in that path and with that name. It exists? Is FileQVD a folder connection that connects to the folder where the qvd is stored?

Re: Incremental Load for Qlik Sense Entreprise on Windows Server 2016

TheKiwi — Mon, 11 Jul 2022 10:56:59 GMT

Hi,
Thank you for your help it works now ! Yeah I thought this script would create the file if it wasn't there. I just had to create it prior to that.

Have a good day !