topic Deduping Records? in QlikView

Deduping Records?

MalcolmCICWF — Fri, 10 Aug 2012 16:45:13 GMT

I am using the below code to load record counts from QVDs and track them. However, when I am making changes and doing multiple reloads a day, I get multiple records of the reload for the same day. I have incremental reloads of other QVDs that only keep the most recent reload record and I would like to do something similar for this code.

So the question is, "How do I keep only the most recent reload when I refresh this QVD multiple times a day"? Do I have to set some sort of system date variable and then some how concatenate only the most recent reload?

Re: Deduping Records?

Miguel_Angel_Baeyens — Mon, 03 Sep 2012 14:48:30 GMT

Hi,

As long as you are concatenating the existing QVD, and storing it into the same QVD file, it will actually save and load each time you perform a reload. If I understand correctly you only want to keep one row per date, with the latest reload as the only row for that date, then I'd do something like the following:

Size:
LOAD ...
AUTOGENERATE 1;
CONCATENATE LOAD
...
FROM QVD_Size.qvd (qvd)
WHERE Date(ReloadTime) <> Today();
STORE Size INTO QVD_Size.qvd (qvd);

This will always load the current values, but will not load the same day line, if any, in the concatenated table, so you always have only one value per day.

Hope that helps.

Miguel

Re: Deduping Records?

MalcolmCICWF — Wed, 26 Sep 2012 16:55:44 GMT

I added in the "WHERE Date(ReloadTime) <> Today();" script and it is still loading multiple reload dates for my report. Since it says <> Today(), every reload is greater than the last because there is time stamps yet... Any ideas?

I jsut need to have one set of records per day kept from teh LAST time the report was reloaded.

Re: Deduping Records?

vinieme12 — Sat, 29 Oct 2016 09:25:53 GMT

Use the concept of incremental load and check where not exist reloadDATE(not time; just date)

Re: Deduping Records?

vinieme12 — Sat, 29 Oct 2016 09:27:22 GMT

Example

LEt reloaddate = NUM(TODAY());

Size:

LOAD ...,

'$(reloaddate)' as ReloadDate

AUTOGENERATE 1;

CONCATENATE LOAD

...

FROM QVD_Size.qvd (qvd)

WHERE not exist (ReloadDate);

STORE Size INTO QVD_Size.qvd (qvd);

Re: Deduping Records?

vinieme12 — Thu, 03 Nov 2016 08:55:46 GMT

if your problem has been resolved can you close this thread?