Delete useless records

[#1]

Hello.

Assume I have loaded a table with n records regarding years 2006, 2007, 2008 and 2009. I need all those records for my calculations but, at the end of the script, I need to keep just years from 2007 to 2009, ie year 2006 records are useless.

How can I delete them, please?

[#2]

The only thing I can think of will add a bit to the total load time, depending on how many records you have. How about a resident load from the original table with a where clause (i.e., where year >= 2007) and then drop the original table?

[#3]

Thank you Jen.

I'm now worried about disk usage, even if I'm talking about several thousend of records, but about that previous years will pollute the overall data (otherwise I'll need to make several filters).

Your solutions unfortunately doesn't work, since if I resident load a table with exactely the same data of another one, QlikView doesn't create it, but just keep the previous one. Thanks anyway for the idea.

[rwunderlich · Accepted solution]

lcortese wrote:
Your solutions unfortunately doesn't work, since if I resident load a table with exactely the same data of another one, QlikView doesn't create it, but just keep the previous one. Thanks anyway for the idea.

Jen's suggestion will work if you use the NOCONCATENATE keyword to force the creation of a new table.

Another approach that may work is a self join.

RIGHT JOIN (data) LOAD DISTINCT *
RESIDENT data
WHERE match(Year, 2006, 2007);

-Rob

[#5]

Thanks, Rob, forgot about that little gotcha - guess I always find something to rename in my load scripts.

[johnw]

I often remove records from existing tables using inner joins to inline loads. It's very fast. In this case, I would expect something like this to do the trick:

INNER JOIN (Data)
LOAD * INLINE [
Year
2007
2008
2009
];

In a real application, you'll probably want to do an autogenerate based on the current date so that you don't have to change anything come 2010 to drop off 2007 (assuming you want it to drop off). Something like this:

INNER JOIN (Data)
LOAD year(today())+recno()-3 as Year
AUTOGENERATE 3
;

If you don't want 2007 to drop off when you hit 2010:

INNER JOIN (Data)
LOAD 2006+recno() as Year
AUTOGENERATE year(today())-2006
;

[#7]

Thank you Jen for the first and fast help. Thanks to Rob for the note about Jen's suggestion and for his proposal. Thanks to John for his always complete and elegant answers.