topic Re: Load oldest date from two tables in QlikView

Load oldest date from two tables

Wed, 06 Nov 2013 10:22:43 GMT

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?

Re: Load oldest date from two tables

swuehl — Wed, 06 Nov 2013 10:43:19 GMT

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;

Re: Load oldest date from two tables

Wed, 06 Nov 2013 13:35:10 GMT

Thank you very much

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

Re: Load oldest date from two tables

Wed, 28 Sep 2016 19:48:02 GMT

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