Automatic definition of daily filenames

In many of my projects my workflows are supposed to read automatically a new file with new data every day; in particular, the workflow has to process the file with yesterday's data. To be sure not to lose information, every day a new file is created with a file name like for example "filename_<yesterday's date>.txt".
In addition, if today is a Monday, the workflow has to read the file with the data (and date in the file name) of last Friday rather than of yesterday. Even more, if last friday was a vacation day, the file to be read must refer to last Thursday.

The automatic definition of the last useful date when data was produced is a very common problem in all programs. In KNIME we could solve this problem by using the lately introduced "Time Series" nodes. However, I was presented with this problem, much before the "Time Series" nodes even existed. The approach I elaborated at that time I find still very useful, so I will give a brief description here.

The file with the list of the country's holidays
First of all I defined a file with the list of the country's holidays, formatted as:

<Date>;  <holiday name>
20100101; First day of the year
20100412; Easter Monday

Date is formatted as "yyyymmdd". This is important, because I will rank all dates to find the holiday in the list closest to today.

The workflow to generate the final filename
I use a workflow variable in the workflow with the path of the file directory, <input_path>. The first step in the workflow is to read the "Holidays" file.

Today is November 22, 2010, Monday. The workflow has to open the file "<input_path>\filename_20101119.txt". The metanode "Workflow vars":

 - creates today's date
- find yesterday's date as last useful date
- if yesterday was a Sunday, then find last Friday's date as last useful date
- if the last useful date was a holiday, define the day before that as last useful date
- generates a column suffix with the last useful date formatted as yyyymmdd

Finally, column suffix is transformed into a workflow variable and passed to a "Variable Based File Reader" node.

Metanode Workflow vars
In the  Java Snippet node, write the following code in the "Global Variable Declaration" in the top frame.

Global Variable Declaration
String suffix = new String();      // create String to host the result
Date now = new Date();                     // today's date
Calendar c1 = Calendar.getInstance();   
int i= 0;                                                         // this index will explore all holiday in the holiday file

In the bottom frame, write the following Java code¨:

Java Snippet code for node "last useful date"
String holiday = new String($Date$);        // <Date> is the column coming from the "Holidays" File Reader node
SimpleDateFormat dateformatYYYYMMDD = new SimpleDateFormat("yyyyMMdd");

if (i==0) { // first row of the "Holidays" file
       c1.setTime(now);
       c1.add(Calendar.DATE,-1);        // go to yesterday 
       int weekday = c1.get(Calendar.DAY_OF_WEEK);  // day of week

// if yesterday was a holiday go to last Friday
      if (weekday == 7)              c1.add(Calendar.DATE,-1);                    // yesterday was Saturday
      else if (weekday == 1)           c1.add(Calendar.DATE,-2);           //yesterday was Sunday
}

/* if <last useful day> is a Holiday */
for(int ht = 0; ht < 2; ht++){
      now = c1.getTime();
      StringBuilder nowYYYYMMDD = new StringBuilder( dateformatYYYYMMDD.format( now ) );
      suffix = nowYYYYMMDD.toString();
      if(holiday.equals(suffix))  {
          c1.add(Calendar.DATE,-1);
      }
}
now = c1.getTime(); 
StringBuilder nowYYYYMMDD2 = new StringBuilder( dateformatYYYYMMDD.format( now ) );
suffix = nowYYYYMMDD2.toString();
 i++;
return suffix;

Write the result into a new string column "suffix".
After executing the node, suffix contains "20101119", which was last Friday.
 

"suffix" represents the <last useful date> and it is calculated for all values in the "Holidays" file rows. If a holiday date meets <last useful date>, <last useful date> is moved one day back. That row in the data table coming out of the "Java Snippet" node has a smaller value of "suffix" than the other rows.

We need then to find the minimum value of "suffix", just in case <last useful date> matched any of the holiday dates. The upper sequence from "String to Number" node to "Number To String" node converts column "suffix" and finds its minimum value.

The last Java Snippet node "filename" just returns the full filepath as <input_path>\filename_<minimum suffix>.

Java Snippet code for node "filename":
return $${Sinput_path}$$ + "/filename_" + $suffix$ + ".txt";