Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

morenoju
Contributor II

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:MMSmiley FrustratedS');

// 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
New Contributor III

Re: QVD file, data concatenation

Issue is in connector not in the script.

So part SQL Select can not be executed properly.

morenoju
Contributor II

Re: QVD file, data concatenation

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

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
Contributor II

Re: QVD file, data concatenation

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

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

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

morenoju
Contributor II

Re: QVD file, data concatenation

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
Contributor II

Re: QVD file, data concatenation

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

Thank you all for your support.

Community Browser