3 Replies Latest reply: Sep 10, 2012 11:59 AM by Rob Wunderlich RSS

    load files daily and combine them to qvds

      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.

        • Re: load files daily and combine them to qvds
          Rob Wunderlich

          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