incremental load and deleting older date from QVD

[Anonymous]

Hi Qlikview experts

Still learning Qlikview tips. I have big QVD file containing over 12 million records and this file will keep growing as data is appended on regular basis.

I need to limit the QVD file so that it is still manageable and only contains 4 weeks worth of data.

I am appending the QVD file with new csv file and just need to append the data from the latest date (e.g. today) and keep the total data to the last 4 weeks only and store them back to new QVD file. So the older date (records) in QVD file will need to be deleted.

What is the criteria that i need to add when loading QVD file and the new csv file.

Thanks for help

[settu_periasamy]

First load your new csv file to find the max date.. and create a variable with previous 4 weeks, then you can use that variable into QVD to load in where field> '$(v4weeksprior)'

Like

Max:

Load max (date) as max from csv;

v4weeksprior=date (weekstart (week (peek ('Max'),-4)));

Drop table Max;

Qvd:

Load * FROM source qvd where datefield > '$(v4weeksprior)';

Concatenate

Load * from csv file;

[Peter_Cammaert]

Maybe you better skip the Max() step and calculate the cutoff date by simply subtracting 4 weeks from today(). Like

LET v4weeksprior = DayStart(Today()) - 28;

Best,

Peter

[maxgro]

// make some test data: qvd and csv file

//

A:

load

  makedate(2015) + floor(rand()*730) as d,

  floor(rand() * 100) as val

AutoGenerate 100000;

STORE A into A.qvd (qvd);

DROP Table A;

CSV:

load

  today() as d,

  floor(rand() * 100) as val

AutoGenerate 10;

STORE CSV into CSV.txt (txt);

DROP Table CSV;

// load 4 weeks, 28 days, including today from qvd file (A.qvd)

// 

FOURWEEKS:

load   date(today() - rowno() + 1) as d  AutoGenerate 28;

A:

load *  from A.qvd (QVD) Where Exists (d);

// add the csv file (today) and store A

//

Concatenate (A)

LOAD d, val FROM CSV.txt  (txt, utf8, embedded labels, delimiter is ',', msq);

// store

STORE A into A.qvd (qvd);

[Anonymous]

Thanks to you all for help and suggestion!

[Anonymous]

Hi Settu,

Your suggestion works, but does dropping the table initially and then loading it again as *. from csv mean reloading it twice?

If that's the case, is there a better way to avoid it.  I have large file to load and would like to avoid loading the same files twice.

[Anonymous · Accepted solution]

Thanks, Peter

It is a good one but the latest file available on the server is not always dated today, so i think I still need to find the maximum date from these csv files, unless there is a better suggestion for this?

[Anonymous]

The script ran without any error message but when I checked the variable value it shows 05/02/1900.

I checked the  max value and it shows the latest time stamp from the scv file.

In short, it always takes the whole  records instead of the last 4 weeks. Any suggestion.

Any suggestion?