Remove rows from table that already identified on other table

[#1]

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

[Anonymous · Accepted solution]

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

[Miguel_Angel_Baeyens]

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

[Miguel_Angel_Baeyens]

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

[Anonymous]

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

[Miguel_Angel_Baeyens]

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