Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
Thank you very much
It was this expression i could not find/understand: WHERE EXISTS(article);
Thank you for this post. It helps me a lot.