Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Remove rows from table that already identified on other table

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
1532012-01-01hello
1632012-01-01
there
1742012-01-02johnruby

QVD_TABLE:

idreport_iddate_of_report
value1
value2
132012-01-01ciao
232012-01-01
faraway
342011-12-31snoopy

RESULT QVD TABLE has to be:

idreport_id
date_of_report
value1
value2
1532012-01-01hello
1632012-01-01
there
342011-12-31snoopy
1742012-01-02johnruby

(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

Tags (1)
1 Solution

Accepted Solutions
divanova
New Contributor III

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

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

5 Replies
divanova
New Contributor III

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

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

MVP
MVP

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

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

MVP
MVP

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

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

divanova
New Contributor III

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

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

MVP
MVP

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

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

Community Browser