Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

goro2010
Contributor

SQL / QVD Incremental Load

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;

1 Solution

Accepted Solutions
ariel_klien
Valued Contributor

Re: SQL / QVD Incremental Load

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

6 Replies
ariel_klien
Valued Contributor

Re: SQL / QVD Incremental Load

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;

BR

Ariel

goro2010
Contributor

Re: SQL / QVD Incremental Load

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

ariel_klien
Valued Contributor

Re: SQL / QVD Incremental Load

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

goro2010
Contributor

Re: SQL / QVD Incremental Load

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

ariel_klien
Valued Contributor

Re: SQL / QVD Incremental Load

yes you can

Re: SQL / QVD Incremental Load

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;