topic Re: SQL / QVD Incremental Load in QlikView

SQL / QVD Incremental Load

goro2010 — Sun, 04 Jun 2017 14:25:21 GMT

Good Day Everyone,

I have an SQL dataset that have over 2 billion lines of data, it took just over 4 hours to download and is just over 2GB in size, I need to update the QVD without downloading the same SQL data everytime but just the changes.

I have a key called "survey_sk" that will be unique between the changes.

I have tried the below but I am getting a syntax error, any help would be appreciated!! - Thank You

"NonTest":

LOAD *, survey_sk as survey_skID

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Concatenate

SQL select *

FROM "62_DW".dbo."t_test" where not exist(survey_sk, survey_skID);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

Re: SQL / QVD Incremental Load

ariel_klien — Sun, 04 Jun 2017 14:37:20 GMT

Hi,

you cannot use QV syntax under SQL sentence.

if survey_sk is a floating number you can try:

"NonTest":

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Temp:

load max (survey_sk ) as MaxId

resident NonTest;

let vMaxId=peek('MaxId');

drop table Temp;

Concatenate (NonTest)

SQL select *

FROM "62_DW".dbo."t_test"

where survey_sk>$(vMaxId);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

Ariel

Re: SQL / QVD Incremental Load

goro2010 — Sun, 04 Jun 2017 14:47:09 GMT

Hi,

Thank you for the reply, the issue is that the survey_sk is not floating, but more random, like it could be 3 now and the next line could be 999, so the peek function I have tried and will not work for this, is there a way that I can do it on date, as there is a field to say, date = 20170604, I don't know if there would be a need for me to load the SQL data on a different way and / or to create an "loaded" indicator or something

I have tried most if not all demo's currently available

Thank You

Re: SQL / QVD Incremental Load

ariel_klien — Mon, 05 Jun 2017 07:00:38 GMT

Yes you can do it on date.

"NonTest":

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Temp:

load max (Date) as MaxDate

resident NonTest;

let vMaxDate=peek('MaxDate');

drop table Temp;

Concatenate (NonTest)

SQL select *

FROM "62_DW".dbo."t_test"

where Date>$(vMaxDate);

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;

if you load partial day you can take the last day like where vMaxDate=peek('MaxDate')-1;

dont forget to load from the qvd date-1

Ariel

Re: SQL / QVD Incremental Load

goro2010 — Mon, 05 Jun 2017 08:18:04 GMT

Good Day Ariel,

I have a date like this on the SQL, 20170605 and the Peek function does not see this as an date, is there a way that I can get the max num?

Thank You

Re: SQL / QVD Incremental Load

ariel_klien — Mon, 05 Jun 2017 08:24:42 GMT

yes you can

Re: SQL / QVD Incremental Load

Kushal_Chawda — Mon, 05 Jun 2017 09:19:25 GMT

try like below

NonTest:

LOAD Concat(DISTINCT chr(39) & survey_sk & chr(39),',') as survey_sk

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

let vSurvey_sk = peek('survey_sk',0,'NonTest');

drop table NonTest;

NonTest:

LOAD *

FROM

C:\Test\Qvd\NonTest.qvd

(qvd);

Concatenate

SQL select *

FROM "62_DW".dbo."t_test" where not in($(vSurvey_sk));

STORE "NonTest" INTO '$(vDataQvds)NonTest.qvd' (qvd);

DROP TABLE NonTest;