topic Re: Incrementally load data from a database in QlikView

Incrementally load data from a database

Tue, 23 Aug 2016 09:14:18 GMT

Dear all,

I'm am loading data from a database into Qlikview using a SQL statement. Normal loading works fine but I would like to implement a load which only loads new records (the column: "ALTERNATIVE_DISPLAY_COLUMN1"; is a column that has a unique ID.

I have tried to accomplish it using the following code:

Test:
LOAD
"ALTERNATIVE_DISPLAY_COLUMN1";

SQL SELECT "ALTERNATIVE_DISPLAY_COLUMN1"
FROM "APEX_040100"."APEX_APPL_LOAD_TABLE_LOOKUPS";

Concatenate
LOAD
"ALTERNATIVE_DISPLAY_COLUMN1";

SQL SELECT "ALTERNATIVE_DISPLAY_COLUMN1"
FROM Sales.qvd(qvd) where not Exists(ALTERNATIVE_DISPLAY_COLUMN1);

Store text into Sales.qvd(qvd);

Drop table Test;

This however does not work. Could anybody advise me on where I go wrong?

Re: Incrementally load data from a database

maxgro — Tue, 23 Aug 2016 09:36:22 GMT

SQL select is for reading from a database, not a qvd file.

So try to replace this

Concatenate
LOAD
"ALTERNATIVE_DISPLAY_COLUMN1";
SQL SELECT "ALTERNATIVE_DISPLAY_COLUMN1"
FROM Sales.qvd(qvd) where not Exists(ALTERNATIVE_DISPLAY_COLUMN1);

with this

Concatenate
LOAD
"ALTERNATIVE_DISPLAY_COLUMN1"
FROM Sales.qvd (qvd)

where not Exists(ALTERNATIVE_DISPLAY_COLUMN1);

Also the store is store Test not Text

Re: Incrementally load data from a database

Tue, 23 Aug 2016 09:40:19 GMT

Hi Massimo,

Thanks for your quick reply. I changed my code with your comments. However now it throws an error saying there is no Sales.qvd file. That makes sense cause I have not creating one yet.

Do you know how I can include something in the code so the .qvd file is generated?

Re: Incrementally load data from a database

Anil_Babu_Samineni — Tue, 23 Aug 2016 09:44:05 GMT

Your directory name is test / text

Can you please have a look of STORE script.

Re: Incrementally load data from a database

Anonymous — Tue, 23 Aug 2016 09:45:16 GMT

Hi - start with creating a base Sales.qvd, once you had the base qvd file, comment out this code as if will no longer be required.

Test:
LOAD
"ALTERNATIVE_DISPLAY_COLUMN1";
SQL SELECT "ALTERNATIVE_DISPLAY_COLUMN1"
FROM "APEX_040100"."APEX_APPL_LOAD_TABLE_LOOKUPS";

Store Test into Sales.qvd (qvd);

Drop table Test;

Re: Incrementally load data from a database

maxgro — Tue, 23 Aug 2016 09:51:25 GMT

Test:
LOAD "ALTERNATIVE_DISPLAY_COLUMN1";
SQL SELECT "ALTERNATIVE_DISPLAY_COLUMN1"
FROM "APEX_040100"."APEX_APPL_LOAD_TABLE_LOOKUPS";

IF QvdCreateTime('Sales.qvd') THEN

trace 'yes'; // file exists

Concatenate (Test)

LOAD
"ALTERNATIVE_DISPLAY_COLUMN1"
FROM Sales.qvd (qvd)

where not Exists (ALTERNATIVE_DISPLAY_COLUMN1);

ELSE

trace 'no'; // file doesn't exist

ENDIF

store Test into Sales.qvd (qvd);

drop table Test;