Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load oldest date from two tables

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

3 Replies
swuehl
MVP
MVP

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;

View solution in original post

Not applicable

Thank you very much

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

Not applicable

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