topic Re: Data Load based on Date in App Development

Data Load based on Date

firmographs — Sat, 16 Nov 2024 03:40:40 GMT

We have a data set that includes both "expired" and "current" data.

"Expired" data would be any record that has an end date prior to today's rate.

In the Load script, we only want to load data that has an expiration date AFTER today's date. That will drop the expired data.

What is the best approach to doing this at the time of loading from a .qvd?

Re: Data Load based on Date

Kushal_Chawda — Sun, 05 Jan 2020 13:27:41 GMT

try like below

Data:

LOAD *

FROM Data.QVD

where floor([end date])>floor(today());

Re: Data Load based on Date

stevedark — Sun, 05 Jan 2020 15:25:24 GMT

Hi,

It depends a bit on the size of your QVD and whether you need your load to be an optimised load or not.

If your QVD is small you can do a WHERE statement, as suggested already.

If you have a large QVD you will want to use a where exists based on all possible future days, something like:

tmpCurrent:
LOAD
Date(today() + RowNo()) as [End Date]
AUTOGENERATE(1000); // set number based on how far in the future your data could possibly go

LOAD
... insert field list here ...
FROM [... your qvd ...] (qvd)
WHERE EXISTS ([End Date]);

DROP TABLE tmpCurrent;

This creates a table of dates that you want to load for and then only loads rows from the QVD where that date is present.

If your End Date field has times in it as well you will need to create a version of the field without the time, e.g.

LOAD
[End Date],
Date(DayStart([End Date]), 'DD MMM YYYY') as [End Date Day],
...

You will then need to use the End Date Day field in your WHERE EXISTS.

Hope that helps.

Steve

Re: Data Load based on Date

firmographs — Sun, 05 Jan 2020 17:49:10 GMT

The load statement has an error

The following error occurred:

Unexpected token: '[Date End NPDES Limit]', expected one of: 'biff', 'dif', 'fix', 'html', 'json', 'kml', 'ooxml', ...

I believe I need to specify that the file type is a .qvd, but there is also a problem with this syntax:

LOAD *
FROM [lib://abcd.qvd]
WHERE floor([Date End])>floor(Today());
(qvd);

How should I tweak this for it to work?

Re: Data Load based on Date

Kushal_Chawda — Sun, 05 Jan 2020 17:59:31 GMT

may be this

LOAD *
FROM [lib://abcd.qvd](qvd)

WHERE floor([Date End])>floor(Today());

Re: Data Load based on Date

stevedark — Sun, 05 Jan 2020 18:10:46 GMT

You need to have the (qvd) before the WHERE statement and only one semi colon. You also need to specify both the library and QVD name separately:

LOAD *
FROM [lib://libname/abcd.qvd] (qvd)
WHERE floor([Date End])>floor(Today());

If you can't get it working please copy and paste the entire load statement, as you have it, into the thread.