Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

incremental load and deleting older date from QVD

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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?

View solution in original post

7 Replies
settu_periasamy
Master III
Master III

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
Partner - Champion III
Partner - Champion III

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
MVP
MVP

// 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
Not applicable
Author

Thanks to you all for help and suggestion!

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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?