Load oldest date from two tables

[#1]

I am quite new to QV but know some sql.

I try to load data from an Oracle database, which is a list over stock articles with shortage that need refilling. I want these data to be stored to a qvd-file with a timestamp.

The next day the same data will be loaded from the Oracle database. Now I will compare the list from the qvd-file and the list from Oracle. Only the articles from Oracle should now be stored back to the file, but the earliest timestamp shall be retained in the file, if the article was present in the file. If the article was not present, it shall be added with new timestamp.

(The application should only see a table representing current shortage combined with ‘First detected’)

example (omitting other data):

File:
article                   timestamp
A20                        30.10.2013
A21                        29.10.2013

New data from Oracle:
article                   timestamp
A20                        06.11.2013
A22                        06.11.2013

Wanted new file:
article                   timestamp
A20                        30.10.2013
A22                        06.11.2013

What is the best approach to achieve this with Qlikview script?

[swuehl · Accepted solution]

It depends on how you define best, but here is one approach:

NEW:

LOAD * INLINE [

article,                   timestamp

A20,                        06.11.2013

A22,                        06.11.2013

];

OLD:

LOAD * INLINE [

article,                   timestamp

A20,                        30.10.2013

A21,                        29.10.2013

] WHERE EXISTS(article);

UPDATED:

NoConcatenate LOAD article, Date(min(timestamp)) as timestamp Resident NEW group by article;

drop table NEW;

[#3]

Thank you very much

It was this expression i could not find/understand: WHERE EXISTS(article);

[#4]

Thank you for this post. It helps me a lot.