load files daily and combine them to qvds

[#1]

Hi there,

I have  a big csv file with transaction logs for the whole 2011 and from january to august( including august) of 2012 that is named 'old_data.csv'

I also have seperate smaller csv files for each of the passed days of september up untill today. Their names are formatted like 'transactions-yyyymmdd.csv

(transactions-20120901.csv , transactions-20120902.csv, etc, etc).

For each day (except saturdays and sundays) i will receive a simillar csv file that will contain transactions from the day before.

The Monday file will include transactions from Friday,Saturday and Sunday.

All of the above mentioned files and the ones to come have the exact same format and field names and they will allways be added to the same folder ( C:/Qlikview_files/transactions_logs).

My goal is to have one qvd file per year, for the years to come.

So i want to combine all the data i have so far into 2 qvds.

One for 2011 and another for 2012 that will be updated daily (except saturdays and sundays) so as to include the new csv files that will be added to the folder.

At January 1st 2013 there should automatically be created a third qvd for that year.

Any help or suggestions would be very appreciated.

Thank you all.

[rwunderlich · Accepted solution]

First one-time build the 2011.qvd by loading the old_data.csv with a WHERE.

Trans:

LOAD * FROM old_data.csv WHERE year(DateField) = 2011;

STORE Trans INTO Trans_2011.qvd (qvd);

Then one-time get the 2012 data from old_data into a seperate csv:

Trans:

LOAD * FROM old_data.csv WHERE year(DateField) = 2012;

STORE Trans INTO transactions-2012.csv (txt);

Now your daily run looks like this.

LET vYear = year(today(1));

Trans:

BUFFER LOAD *

FROM [transactions-$(vYear)*.csv];

STORE Trans INTO Trans_$(vYear).qvd (qvd);

Note above is not complete FROM syntax, but hopefully close enough to make the point.

-Rob

[#3]

Hi there, your answer really helped me a lot. Thanks a lot man!

Now, another question rises for me.

Is there a limit on the minimun number of records that a qvd must include?

In other words, should i consider creating qvds at how many records?

50.000, 100.000, 500.000?

Or is the size of the files more important than the number of records?

Thanks.

[rwunderlich]

There is no minimum or maximum number of records for a QVD. Larger files will take longer to read and write. So it's a tradeoff between performance and convienence of use.

-Rob