Including variable data in filters and filenames

Question
1

This might be a feature request…

I have a (actually quite simple) transform that I run at the beginning of every month – I get an Excel report emailed that I build two outputs from.

Both outputs – ideally – should have date info in their filenames. The input file is always named the same way so I can’t use filename variables in their current form, but I could probably build something with the available metadata if I could do a bit of string slicing on it…?

The second output has a filter that just extracts data from the for the previous month. As it’s November 2022 as I write this, the current form of the filter is "OpDate >= “2022-10-01” but I’d like it to be calculated so I don’t have to remember to amend the filter…

I am aware, as I write this, that I might be asking for a heck of a lot of work to solve a problem that is human-fixable with a couple of tiny changes to easily-typed text …

2

You can do that in more recent versions of Easy Data Transform:

image
image993×689 52.2 KB

You can do this as well, with a bit of cunning:

image
image668×654 125 KB

Then calculate months and years. Finally use data from the table for filtering:

image
image1327×504 153 KB

So it should be possible to automate it all.

image
image1348×108 58.9 KB

Simple example here:

last-month-filter.transform (5.0 KB)
input.csv (90 Bytes)

Note that the 5 Calculate transforms are for date manipulation.

2 Likes
3

Thanks, Andy!

I’ve tried to be clever with year rollovers. I’ve built a concatenated date field from calculated columns (if subtracting 1 from the month gives zero then the answer should be 12 and the year one less than the current year) with a couple of If transforms, and finally concatenated the year, month and day into a date field that I can use in the final filter, and it works brilliantly!

The detail, I’ve made a column using the logic of your calculated fields, renamed the meta month and year fields to CurrentMonth and CurrentYear, calculated a new field called DecYear by subtracting 1 from CurrentYear, added an IF transform that compares CurrentMonth to 0 and sets it to 12 or $(CurrentMonth) and called that column CorrectedMonth, another IF called CorrectedYear that also compares CurrentMonth to 0 and sets the result to $(DecYear) or $(CurrentYear), made a new column called StartDay and set it to 01.

Then I used a reorder transform for those three so CorrectedYear comes first, then CorrectedMonth then StartDay.

Then a Concatenate transform with a separator of a dash symbol, making a new column that builds a date from the three fields, then a rename of the result to StartDate, and used $(StartDate) as the basis of the old Filter transform.

What I didn’t expect (!) was for that last bit to work, if I’m honest, as it was, in my head anyway, a string value that just looked like a date. But it did!!!

I have just fallen a little further in love with EDT. :smile:

1 Like