I am trying to work out a way to filter for specific dates in the file and run the process only if that condition is satisfied, but I haven’t been successful so far so any help would be greatly appreciated.
Here’s what the issue is:
So we receive files with the dates 14/MM/yy and 30/MM/yy. in them along with other data.
To clarify the file would look something like this:
File name:TR101.xlsx
Date Amount Name
14/MM/yy 12000 xyz
And if it is sent on the 30th (if it is a bank holiday or a weekend then the next working day)
File name: TR101.xlsx
Date Amount Name
30/MM/yy 150000 abc
But the files could be sent on any date, not just those dates mentioned above. Also, those dates could vary i.e., if it’s a weekend or a bank holiday then the next working day would be the date in the file.
I want to create a process which filters on the date in the file (the date is not in the filename) checks if it is either 14th or 30th and then exports it. If not it sends a message no files to load or something.
Yes, it’s surely possible. Cases like that is the perfect use case for EasyMorph.
Here is a simple example that reads TR101.xlsx, checks the date in its data, and exports the data into a database if the day is 14th or 30th (and skips exporting for other days).
If you need a more complex exporting logic, then you can put it into a separate module, and call that module using the “Call” action right after the “Skip on condition” action.
I had used the filter by condition action and used the following expression:
contains([Date], 30) or contains ([Date], 14)
and it was able to do what I was hoping for, i.e. check if the dates in the file are either 14 or 30th but the issue is what if 14th or 30th is a bank holiday or a weekend? Then the date in the file won’t be 14th/30th but the next business day.
How do I use that in an expression?
Sorry for my poor explanation and thank you for so kindly helping me with this so promptly.
Then you need to have a list (calendar) of holidays and weekends (e.g. for the whole year or several years) and have the "required" dates marked as "Export" (or something else) in that list. I would imagine this calendar having 2 columns - dates, and "Export / No export" labels for each date.
Once you have this calendar loaded, you can use the "Lookup" action to match the date from a file with the calendar. If the date is labelled as "Export", then do export. If not then skip exporting.
