topic Re: Remove rows from table that already identified on other table in QlikView

Remove rows from table that already identified on other table

Thu, 26 Jan 2012 08:47:54 GMT

Hi,

I have a QVD file with data identified on [report_id, date_of_report]. Let us call this QVD_TABLE.

I now retrieve new records from the database. Let us call this DB_TABLE.

QVD_TABLE and DB_TABLE have the same structure.

I want to add to QVD_TABLE new rows coming from DB_TABLE but at the same time carry out a refresh, as follows:

I want to end with QVD_TABLE holding the rows that it had initially, plus all the new rows from DB_TABLE, but for those rows in DB_TABLE that they have the same [report_id, date_of_report] to keep the DB_TABLE rows and remove the QVD_TABLE rows.

Example:

DB_TABLE:

SQL SELECT

id,

report_id,

date_of_report,

value1,

value2

FROM report_results;

QVD_TABLE:

LOAD

id,

report_id,

date_of_report,

value1,

value2

FROM $(vQvdFile) (qvd);

Example Data:

DB_TABLE:

id	report_id	date_of_report	value1	value2
15	3	2012-01-01	hello
16	3	2012-01-01		there
17	4	2012-01-02	john	ruby

QVD_TABLE:

id	report_id	date_of_report	value1	value2
1	3	2012-01-01	ciao
2	3	2012-01-01		faraway
3	4	2011-12-31	snoopy

RESULT QVD TABLE has to be:

id	report_id	date_of_report	value1	value2
15	3	2012-01-01	hello
16	3	2012-01-01		there
3	4	2011-12-31	snoopy
17	4	2012-01-02	john	ruby

(I have put in red the rows of QVD TABLE that have been deleted and replaced by the blue rows of DATA_TABLE)

Now, how can I refresh QVD_TABLE as I described above?

Thanks in advance

Panayotis

Re: Remove rows from table that already identified on other table

Anonymous — Thu, 26 Jan 2012 09:57:55 GMT

Hi Panayotis,

You can try this:

QVD_TABLE:

//DB_TABLE:

LOAD

date_of_report & report_id as key,

FROM report_results;

CONCATENATE

//stored QVD_TABLE:

LOAD

date_of_report & report_id as key,

FROM $(vQvdFile) (qvd)

WHERE NOT EXISTS (key, date_of_report & report_id);

STORE QVD_TABLE INTO $(vQvdFile) (qvd);

Let me know if it does what you need please;

Dilyana

Re: Remove rows from table that already identified on other table

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 10:10:51 GMT

Hi Panayotis,

Consider the script below. In the first load, QVD_TABLE is load as it is. In the secod, the right keep will cause the the table to have only existing records based on date but with different "id". The third will add those existing records based on date from the first table, and the fourth load will add new records based on date. Check the application where this script is executed.

QVD_TABLE:
LOAD *, report_id & '$$' & date_of_report AS key1 INLINE [
id, report_id, date_of_report, value1, value2
1, 3, 2012-01-01, ciao 
2, 3, 2012-01-01, , faraway
3, 4, 2011-12-31, snoopy 
];
RIGHT JOIN (QVD_TABLE) LOAD *, report_id & '$$' & date_of_report AS key1
INLINE [
id, report_id, date_of_report, value1, value2
15, 3, 2012-01-01, hello 
16, 3, 2012-01-01, , there
17, 4, 2012-01-02, john, ruby
] WHERE EXISTS(date_of_report);
DB_TABLE:
CONCATENATE (QVD_TABLE) LOAD *, report_id & '$$' & date_of_report AS key1 INLINE [
id, report_id, date_of_report, value1, value2
15, 3, 2012-01-01, hello 
16, 3, 2012-01-01, , there
17, 4, 2012-01-02, john, ruby
]
WHERE NOT EXISTS(date_of_report);
CONCATENATE (QVD_TABLE) LOAD *, report_id & '$$' & date_of_report AS key1 INLINE [
id, report_id, date_of_report, value1, value2
1, 3, 2012-01-01, ciao 
2, 3, 2012-01-01, , faraway
3, 4, 2011-12-31, snoopy 
] WHERE NOT EXISTS(date_of_report);

Hope that helps.

Miguel

Re: Remove rows from table that already identified on other table

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 10:12:46 GMT

Hi Dilyana,

That's right and will load all records from the first table and append (not replace) those records from the second. So all records with the same date in the first table will be kept, instead of loading from the DB.

Regards.

Miguel

Re: Remove rows from table that already identified on other table

Anonymous — Thu, 26 Jan 2012 10:20:02 GMT

Hi Miguel,

It loads the new table first and the old one second. This way the updated version of the records will be kept and the older one discarded.

Regards,

Dilyana

Re: Remove rows from table that already identified on other table

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 10:40:19 GMT

Hi Dilyana,

Sorry, you are completely right. Your solution is the correct one: quite faster and much less complicated than mine, you know, I'm having one of those bad brain days.

Regards.

Miguel