Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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