Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Piro822
Contributor
Contributor

Append data to an existing table

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.

1 Solution

Accepted Solutions
anushree1
Specialist II
Specialist II

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

View solution in original post

12 Replies
uacg0009
Partner - Specialist
Partner - Specialist

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
Piro822
Contributor
Contributor
Author

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.
anushree1
Specialist II
Specialist II

Could you post the error encountered.

 

Piro822
Contributor
Contributor
Author

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.
uacg0009
Partner - Specialist
Partner - Specialist

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
Piro822
Contributor
Contributor
Author

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.

anushree1
Specialist II
Specialist II

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

Piro822
Contributor
Contributor
Author

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.

Piro822
Contributor
Contributor
Author

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.