Directory Tool

[suby]

All,

I'm trying to use the Directory Tool to list all the CSV files and then Parsing the Date in the FileDate column as i am able to see all the list of files in both the Directory tool output and the Formula Tool output.

And then I'm trying to filter for current Date and Past 5 months and I'm getting no results on the T filter tool.

Directory Tool
xx xxxxxxxx xxxx_*.csv

FileDate
DateTimePArse(REGEX_Replace([FileName],'.*_(.*)\.csv.*', '$1'),'%Y-%m-%d')

Filter Tool

//current date
toDate([FileDate]) IN( DateTimeToday(),

//First of the current month
toDate(DateTimeFirstOfMonth())

//First of the current month -1
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-1,'month'))
//First of the current month -2
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-2,'month'))
//First of the current month -3
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-3,'month'))
//First of the current month -4
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-4,'month'))
)

Any help would be much appreciated.

Thanks

[binuacs]

@suby Can you provide the CSV file name format?

[suby]

Hi Binuacs,

The file name looks like this 

xx xxxxxxx xxxxx_2023-05-15 00_00_00

Thanks

[binuacs]

@suby Try the below regex

REGEX_Replace([File Name], '.*_(\d{4}\-\d{2}\-\d{2}).*', '$1')

[suby]

Thanks Binuacs it works.

Just to clarify one more thing on the filter logic below I want to include the file as of yesterday's date file I tried this logic but its not bringing the Yesterdays' file but it brings the past 5 Months Date File.

//current date
toDate([Data Date]) IN (DateTimeAdd(DateTimeToday(),-1,"days"),

//First of the current month
toDate(DateTimeFirstOfMonth())

//First of the current month -1
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-1,'month'))
//First of the current month -2
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-2,'month'))
//First of the current month -3
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-3,'month'))
//First of the current month -4
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-4,'month'))
)

[binuacs]

@suby First confirm whether the field [Data Date] brings yesterday's file date or not in the directory tool

[suby]

hi Binuacs,

Thanks i did checked the directory is bringing the Yesterday's file.

But I'm using the below  logic and its brings up the other past 5 Months but not the Yesterdays file am I doing anything wrong ?

The logic colour coded is the one which is causing the issue

//current date
toDate([Data Date]) IN (DateTimeAdd(DateTimeToday(),-1,"days"),

//First of the current month
toDate(DateTimeFirstOfMonth())

//First of the current month -1
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-1,'month'))
//First of the current month -2
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-2,'month'))
//First of the current month -3
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-3,'month'))
//First of the current month -4
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-4,'month'))
)

[binuacs · Accepted solution]

@suby Can you try the below formula 

//current date
toDate([Data Date]) IN toDate((DateTimeAdd(DateTimeToday(),-1,"days")),

//First of the current month
toDate(DateTimeFirstOfMonth())

//First of the current month -1
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-1,'month'))
//First of the current month -2
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-2,'month'))
//First of the current month -3
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-3,'month'))
//First of the current month -4
, toDate(DateTimeAdd(DateTimeFirstOfMonth(),-4,'month'))
)

[suby · Accepted solution]

Hi Binuacs,

the formula is still not working any thoughts ?

[binuacs]

@suby @Can you check the field [Data Date] has yesterday’s date? Also confirm the data type of this field? Is it date format or string format? If it is string format then you need to update your condition like below in the filter tool

ToDate(DateTimeParse([Data Date], ‘%Y-%m-%d))