topic Re: Append data to an existing table in QlikView

Append data to an existing table

Piro822 — Tue, 22 Jan 2019 09:08:23 GMT

Hello,

Is it possible to append data to an existing table using a select?

Let's say that I have in a table A data from yesterday and I want to do a select for updating the table A with new data from today without loosing the data from yesterday. I noticed that when selecting data from today it replaces the whole table.

Thank you in advanced for your help.

Re: Append data to an existing table

uacg0009 — Tue, 22 Jan 2019 09:32:46 GMT

Hi Piro,
You can use "concatenate", like:
table_yesterday:
load * from your_source;
concatenate
load * from your_source
where date = today();

something like above script
Aiolos Zhao

Re: Append data to an existing table

Piro822 — Tue, 22 Jan 2019 09:51:13 GMT

Thank you for your answer but it gave me problems in the script :

This is my script :

[PT_TABLE_A]:
LOAD * from PT_TABLE_A;

CONCATENATE

LOAD FIELD1,
FIELD2,
FIELD3;
SELECT "FIELD1",
"FIELD2",
"FIELD3"
FROM "MYDATABES"."TABLE_A" WHERE FIELD3 between TO_DATE('2017-01-01 14:22:00','YYYY-MM-DD HH24:MI:SS')
and TO_DATE('2017-01-01 14:23:00','YYYY-MM-DD HH24:MI:SS');

I need to do a SELECT for bringing for example the last minute but keeping the data that I already collect before.

I do not know if I can do it.

Re: Append data to an existing table

anushree1 — Tue, 22 Jan 2019 10:20:23 GMT

Could you post the error encountered.

Re: Append data to an existing table

Piro822 — Tue, 22 Jan 2019 10:23:29 GMT

Connected

TABLE_A << QueryResult Lines fetched: 0

Connected

The following error occurred:

No qualified path for file: ***

The error occurred here:

[TABLE_A]:

LOAD * from TABLE_A

Data has not been loaded. Please correct the error and try loading again.

Re: Append data to an existing table

uacg0009 — Tue, 22 Jan 2019 10:27:07 GMT

So your problem now is how to find the last minute, right?
If yes, you can create a variable to get the last minute from your above table, then use it in the bottom table.
because using concatenate I think you can append data as you want.
Aiolos Zhao

Re: Append data to an existing table

Piro822 — Tue, 22 Jan 2019 10:40:35 GMT

I think the problem if you see in the error is that it does not get the old information (the one that I have already in Qlik Sense with a table_A) . If I execute separetly this one :

LOAD FIELD1,
FIELD2,
FIELD3;

SELECT "FIELD1",
"FIELD2",
"FIELD3"
FROM "DATABASE"."TABLE_A" WHERE FIELD3 between TO_DATE('2017-01-01 14:22:00','YYYY-MM-DD HH24:MI:SS')
and TO_DATE('2017-01-01 14:23:00','YYYY-MM-DD HH24:MI:SS');

If I change the date (Field3) all the time , it does not give me anytype of problem . Collect the rows from the database and delete the previus ones. The problem is keeping the old ones and take the new ones. It is like I can not add it new information to the same table witout deleting the old one with the script.

I tried the concatenate like you told me but it gives a error in the first load (the one that take all the old table).

Thank you for the help.

Re: Append data to an existing table

anushree1 — Tue, 22 Jan 2019 11:19:59 GMT

Hi,

The Solution to your problem is using incremental load please go through the link https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Also attached , is the pdf with a slight complex script but can be caught up with once you get handy with the link shared

Re: Append data to an existing table

Piro822 — Tue, 22 Jan 2019 11:54:54 GMT

Thank you so much for the answer!!! yes !! I saw just now in the tutorial that I think that it is the same information that you have given to me. It looks complex but it is worth a try !! I will do the test.

Thank you.

Re: Append data to an existing table

Piro822 — Tue, 22 Jan 2019 12:43:00 GMT

So I have created like in the example with incrementals this script :

LIB CONNECT TO 'Oracle_10.6.3.31';

TABLE_A:
SELECT FIELD1,
FIELD2,
FIELD3
FROM "DATABASE"."TABLE_A" WHERE FIELD3 between TO_DATE('2017-01-01 14:22:00','YYYY-MM-DD HH24:MI:SS')
and TO_DATE('2017-01-01 14:23:00','YYYY-MM-DD HH24:MI:SS');

    Concatenate FIELD1,
FIELD2,
   FIELD3 FROM MyFile.qvd(qvd);

STORE TABLE A INTO MyFile.qvd (qvd);

And I get this error when I load data:

Connected

TABLE_A << QueryResult Lines fetched: 0

Connected

TABLE_A << QueryResult Lines fetched: 112

The following error occurred:

No qualified path for file: ***

The error occurred here:

Concatenate LOAD FIELD1, FIELD2, FIELD3 FROM MyFile.qvd(qvd)

Data has not been loaded. Please correct the error and try loading again.

Any ideas how to get the .qvd file? I see in the help of Qlik that the command store does it automatically.

Highly appreciated the help.

Re: Append data to an existing table

Piro822 — Tue, 22 Jan 2019 14:08:08 GMT

Hello again,

Finally I have found the solution!!! I have to put lib:// with an existing folder in my data connections. Then it works.

Thank you so much for all the help.

Re: Append data to an existing table

anushree1 — Wed, 23 Jan 2019 07:50:41 GMT

Well in this case I recommend , you check few things here

1. In case you are concatenating the data, it means you must have had the old data in the qvd file and it must be existent

2. Check if the path specified for the qvd is correct, you can check this by trying to load from the qvd on another tab and see the path

3. In case you are using QV 12 , few users are seen to have reported this issue , and have said it is solved either when close and reopen the sample and try this action, and also by not using relative path. Please try the above and check if it helps

Re: Append data to an existing table

Piro822 — Wed, 23 Jan 2019 07:53:08 GMT

Thank you so much for the answer!!! yes...yesterday I saw the solution like you say in the Qlik help and now it is working.

Regards.