Data Load based on Date

[firmographs]

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?

[Kushal_Chawda]

try like below

Data:

LOAD *

FROM Data.QVD

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

[stevedark]

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

[firmographs]

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?

[Kushal_Chawda]

may be this

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

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

[stevedark · Accepted solution]

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.