topic Re: Delete data from a QVD in QlikView

Delete data from a QVD

Fri, 06 Dec 2013 12:38:45 GMT

Hi all,

I load data monthly in a single QVD file and one of the months is incorrect.

Is there a way to delete information from a QVD in order to reload the correct data?

Or will I have to create a new QVD file?

Re: Delete data from a QVD

Fri, 06 Dec 2013 12:40:15 GMT

Sean,

You can add a WHERE statement to not load this particular month.

Reload this month from your source

Recreate the QVD file for next time

Fabrice

Re: Delete data from a QVD

MK_QSL — Fri, 06 Dec 2013 12:41:33 GMT

TABLENAME:

Load * from TABLENAMEQVD where Month = YourWrongMonth;

Restore TABLENAME into TABLENAME.qvd (QVD);

Re: Delete data from a QVD

Fri, 06 Dec 2013 12:46:48 GMT

Hahaha I don't know why I didn't even think of that.. Silly me! Thanks for the assistance guys

Re: Delete data from a QVD

Fri, 06 Dec 2013 12:50:03 GMT

tag the answer as correct and close the discussion then

Fabrice

Re: Delete data from a QVD

Fri, 06 Dec 2013 13:12:21 GMT

LOAD sbsFileName,

FileTime,

[TERM-ID],

[BATCH-SEQ],

DT,

month(DT) as Month,

year(DT) as Year,

SbsRunDT,

TIME,

TRAN,

CARDHOLDER,

REFERENCE,

AUTH,

Amount_SBSA,

[TRACE NO],

FROM

(qvd)

Where year(DT) = 2013 and month(DT) <> 'Oct'

It isn't reading the '<>' function and its still loading Oct Results? Help

Re: Delete data from a QVD

johnca — Fri, 06 Dec 2013 13:25:55 GMT

That looks like it should work.

What does DT and month(DT) look like?

Re: Delete data from a QVD

Fri, 06 Dec 2013 13:29:41 GMT

Hi John,

I got that to work but now I'm stuck with another issue.

Because I need October and Novemembers data for 2010, 2011, 2012 but not 2013 I've decided to first load all data from year=2013 and month <> Oct and Nov

It created a new file for me called sbsloadfix.qvd

Now I'd like to load all of 2010,2011,2012 into that same QVD file however if I rerun the script using the where year = 2012 it'll then overwrite the 2013 data..

Any idea how to do a Concatenate store?

Re: Delete data from a QVD

johnca — Fri, 06 Dec 2013 14:01:09 GMT

Hi Sean,

After you load the good data from Oct & Nov 2013 do a load on all the data where not exists (DT).

Sometimes it's easiest to save the good data into QVD then drop that table and reload it, then do the where not exists.

--John

Re: Delete data from a QVD

Fri, 06 Dec 2013 14:08:32 GMT

... where year(DT) <> 2013 or month(DT) <> 'Oct'

Fabrice

Re: Delete data from a QVD

stevedark — Fri, 06 Dec 2013 14:08:34 GMT

STORE will always write away whatever is in the table in memory at that time. To load from many QVDs and store into one simply load from each QVD in turn and then store the resulting table.

Steve

Re: Delete data from a QVD

Fri, 06 Dec 2013 14:10:43 GMT

Thanks for the replies guys.. You've been very helpful.. Basically I loaded from the QVD first time with the where clause for 2013 only.. Then did a concatenate load 3 more times for each other year and stored the results at the end..

Worked perfectly..

Appreciate the assistance!

Re: Delete data from a QVD

Fri, 06 Dec 2013 14:20:31 GMT

Tags one or several answers as usefull or correct then

Fabrice