topic Delete useless records in QlikView

Delete useless records

Fri, 18 Dec 2009 22:51:16 GMT

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?

Delete useless records

Fri, 18 Dec 2009 23:23:33 GMT

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?

Delete useless records

Fri, 18 Dec 2009 23:38:53 GMT

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.

Delete useless records

rwunderlich — Sat, 19 Dec 2009 04:01:18 GMT


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

Delete useless records

Sat, 19 Dec 2009 04:53:29 GMT

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

Delete useless records

johnw — Sat, 19 Dec 2009 05:06:16 GMT

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
;

Delete useless records

Mon, 21 Dec 2009 15:56:08 GMT

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.