Update Fanout expression name with date

Hi!

If you always need the value of the previous month in the format “2024-07” (%Y-%m) you can maybe try this expression:

@DateTimeFormat(@DateTimeAdd(@DateTimeNow(),-P1M),%Y-%m)

It gets the date from today (DateTimeNow),

Calculates one month back (DateTimeAdd, -P1M)

and formats the date into your desired datetime format (DateTimeFormat).

This can also be done using the DateTimeStamper, DateTimeCalculator, and DateTimeConverter if you would prefer transformers over an expression.

Hi veschwab  

Sorry for the delayed response, I had some other work take priority and leave so I’ve only now just had another look at the FME workbench I was testing. 

So not sure I’ve done this right but I amended the Writer Fanout Expression to the following:
R@Value(billing_auth_id)-@DateTimeFormat(@DateTimeAdd(@DateTimeNow(),-P1M),%Y-%m)-FileName.xlsx

Ran the workbench and tested one output file and the file saved with the previous month and year in the filename RXXXX-2024-07-FILENAME.xlsx which was good but then when I compared the contents populated in the data with the original run before the Fanout expression was changed where I was manually updating the month and year, I seem to have more records in the test run with duplicate entries which is odd as the reader data has not been updated since the start of the month? 

Going to do some more checks to see why I’m not getting an identical output?
 

Hi liamfez,

Thank you for your response as well, apologises for the delayed reply. I had some other work take priority and leave so I’ve only now just had another look at the FME workbench I was testing.

So in the workbench where i had 2 readers, 2 writers, a sorter and test filter, I then tried adding the following transformers after the test filter for DateTimeStamper, DateTimeConverter, DateTimeCalculator, and then DateTimeConverter_Output . It did work but I was then getting the attribute name year_month outputting into the excel output even when I took it out and it was not subtracting the month by 1 so it was saving the file as 2024-08 instead of 2024-07.

Talking with a colleague we then tried linking the attribute manager to the test filter and creating the following 4 attributes:
 

Then linked a Tester to the Attribute Manager and ran it, it seems to work but was sifting the data in the spreadsheet over so I have to check the alignment of the columns with the data has that has changed.

If the Excel file already existed, and the Writer was set to the default of Overwrite Existing File = No, then the Writer will add its features to already existing Workbook/Worksheet and create duplicate rows in the existing Excel file.

I’ve lost count of the amount of times I’ve forgotten to delete the original writer output when testing and scratched my head as to why more rows than I would have thought in the output.

The Dataset Fanout shouldn’t have any

Adding the time function should not impact which data is written out.

Try and look up, like @bwn said, the settings you have for Overwrite Existing File.

I renamed the original file as ORIGRUN_01082024 before running the FME workbench when I tried rerunning once the fanout expression had changed. I did realise that when I first tried it. Although it was a good point to check!

Is it just duplicates that are the problem? Or do you get in other data that should not be included in the particular fanout?

It was rather odd, as there were duplicates when there shouldn’t have been but some of the data in the spreadsheet went out of alignment to what was in the template headers.

What I ended up doing was recreating the FME workbench from scratch in case I corrupted the previous one by playing around trying to get it to work.

The recreated workbench worked, the output files matched the manual original files ran at the start of the month and the data has outputted in the correct tab sheets and under the correct headers, no duplications and row counts match! 

So that is good news!

I need to look at two other workbenches and try to do the same thing but one has the date showing as 31Jul2024. The other is a weekly job that has dates from Thursday to Wednesday so it would show as 15Aug2024-21Aug2024. So that will be next ones to figure out?

Ok, great that it works now.

The other two examples you are giving should be also possible to work with DateTime Functions. Here is some more information about it.

If you just need to change the format of the date you can use the @DateTimeFormat function again. You can see how to do the formatting under the headline Format String Flags and Examples on the website I linked above.

For the other example you have just concatenate two DateTime functions and put a ‘-’ in between then.