topic Re: QVD file, data concatenation in QlikView

QVD file, data concatenation

morenoju — Wed, 29 Nov 2017 14:47:50 GMT

Hi all,

I'm bringing a lot of records (about 50 million) from a table in a Vertica DB. The table is expected to keep growing, so I want to use a concatenated load script and a qvd file to avoid having to spend too much time for each load.

I' ve been trying to replicate what is explained on this post Incremental reload and store into QVD file but in the end, I keep getting the same error:

The following error occurred:

Connector reply error: ***

The error occurred here:

Would you have a look on my script and let me know if there's something I can fix?

Thanks!

Juan

---

LET lastReloadTime = Date(ReloadTime(),'YYYY-MM-DD HH:MM:SS');

// If traffic.qvd already exists, we load its data

IF qvdcreatetime("D:\Temp\data_traffic\traffic.qvd") Then

[traffic]:

LOAD "start_date" as link_timestamp,

"item_id" as [link.id],

speed,

"speed_mph",

traveltime

FROM (qvd);

ELSE

// If traffic.qvd does not exist, we create it with a SQL query

[traffic]:

LOAD "start_date" as link_timestamp,

"item_id" as [link.id],

speed,

"speed_mph",

traveltime;

SQL SELECT "start_date",

"item_id",

speed,

"speed_mph",

traveltime

FROM pems."public"."measure_link_15min";

END IF

// We concatenate any new data (data whose link_timestamp is later than the lastReloadTime)

CONCATENATE ([traffic])

LOAD "start_date" as link_timestamp,

"item_id" as [link.id],

speed,

"speed_mph",

traveltime;

SQL SELECT "start_date",

"item_id",

speed,

"speed_mph",

traveltime

FROM pems."public"."measure_link_15min"

WHERE (link_timestamp > $(lastReloadTime));

STORE [traffic] INTO

Re: QVD file, data concatenation

hubertdudek — Wed, 29 Nov 2017 14:53:56 GMT

Issue is in connector not in the script.

So part SQL Select can not be executed properly.

Re: QVD file, data concatenation

morenoju — Wed, 29 Nov 2017 15:28:22 GMT

Right, I changed the WHERE clause:

WHERE ("start_date" > $(lastReloadTime));

I was using link_timestamp by mistake. However, I'm getting the same error....

Re: QVD file, data concatenation

rubenmarin — Wed, 29 Nov 2017 15:36:27 GMT

Hi Juan, note that the query passed will be:

WHERE ("start_date"> 2017-11-29 16:32:30);

¿Can this work on Vertica DB? Syntaxis will depend on database, not QlikView

Re: QVD file, data concatenation

morenoju — Wed, 29 Nov 2017 15:55:46 GMT

Hi Ruben. I've checked and in Vertica I need apostrophes for the date:

WHERE ("start_date"> '2017-11-29 16:32:30');

I've added them in the script, but unfortunately I'm getting the same error

WHERE (start_date > '$(lastReloadTime'));

Re: QVD file, data concatenation

rubenmarin — Wed, 29 Nov 2017 15:57:49 GMT

Hi Juan, I don't know if it's a typo, but it should be:

WHERE (start_date > '$(lastReloadTime)'); //closing apostrophe moved

Re: QVD file, data concatenation

morenoju — Wed, 29 Nov 2017 16:05:21 GMT

Wow, good catch! Thank you Ruben!

Now I got a different error, I'm going to try to solve it and I'll post here the conclusions.

Thanks!

Re: QVD file, data concatenation

morenoju — Wed, 29 Nov 2017 17:12:03 GMT

I solved some other issues non related to the connector and it worked.

Thank you all for your support.