Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
morenoju
Partner - Specialist
Partner - Specialist

QVD file, data concatenation

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

7 Replies
hubertdudek
Contributor III
Contributor III

Issue is in connector not in the script.

So part SQL Select can not be executed properly.

morenoju
Partner - Specialist
Partner - Specialist
Author

Right, I changed the WHERE clause:

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

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

rubenmarin

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

morenoju
Partner - Specialist
Partner - Specialist
Author

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

rubenmarin

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

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

morenoju
Partner - Specialist
Partner - Specialist
Author

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!

morenoju
Partner - Specialist
Partner - Specialist
Author

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

Thank you all for your support.