If condition - Date

[Cormac (Cormac White)]

Hello - Can anyone help me with the following?

I am getting a structured data table which looks like the below image when I write to CSV. I am then trying to use an if condition to only download files that relate to yesterday’s date so in this case as today is 15/01/2020 I want to only download files that have the date 14/01/2020 from column Received/Track.

Does any know how to write that condition? The second part is that it Today is Monday then I want to download files from Friday Saturday and Sunday - is this possible in the if condition?

Thanks

[YAZIDI (YAZIDI Abdelhadi)]

Hi @Cormac,

For the first condition you can use:

dateToCompare.Date = Today.AddDays(-1).Date

And for the second one use:

CInt(dateToCompare.DayOfWeek) = 1

Regards

[WhenCutEsh (Venkatesh Nallana)]

@Cormac

1. Filter your table Received/Track Column with Yesterdays Date now.AddDays(-1).ToString(“dd/MM/yyyy”)
2. use the DayOfWeek Method to find if its Monday, if its filter between Received/Track Column
   now.AddDays(-3).ToString(“dd/MM/yyyy”) to now.ToString(“dd/MM/yyyy”)

[Cormac (Cormac White)]

Thanks for the response

I have the first part entered into the condition of the IF activity and it seems to work. Can I enter the second part into the same IF activity? If so how?

[Dave (David Wiebelhaus)]

@Cormac It’s always possible to combine many ifs, but for readability it is better to separate into different if statements.

Personally, I would have 2 date variables I’ll call startDate and endDate. This allows you to specify a range to download. This range can be a single day (Jan 15 at 12:00am through Jan 15 at 11:59pm) or it can encompass a weekend (Jan 10 at 12:00am through Jan 12 at 11:59pm). It also makes it so only a single if statement is required

Your if statement should determine if today is Monday. If today is monday that means we want to compare fri-sun instead of only the previous day. That can be done with a statement similar to the one provided by @YAZIDI which is: CInt(today.DayOfWeek) = 1

If this is true (meaning today is monday) then on the true side, you want to set the startDate to be friday at midnight. On the false side you would set the startDate to be the previous day at midnight. Whether it’s true or false, we want the endDate to be the previous day at 11:59pm

True side:
Assign startDate = today.addDays(-3)

False side:
Assign startDate = today.addDays(-1)

Outside of the if statement:
Assign endDate = today

Now when you gather the the files you can supply the date range that is greater than or equal to the startDate and less than (not less than or equal to!) the endDate.

Something to keep in mind too - whenever you are working with dates you should also be cognizant of dates changing midway through your workflow if you start it close to the end of the day, or if it is extremely long running. You may wish to assign a static variable containing today’s date at the beginning of your workflow instead of using the dynamic ‘today’ which calculates the current day at that specific point during runtime

[Cormac (Cormac White)]

@Dave - Thanks for the detailed response. All of that makes sense.

[Cormac (Cormac White)]

Hi @Dave - one more question if I use the code startDate = today.addDays(-3) will that just download all files relating to the date three days ago or will it download files relating to three, two and one day ago?

[Dave (David Wiebelhaus)]

@Cormac The information/code i posted above only gives you a date range to use later in your workflow to choose actual files. In vb.net, today is just a shortcut way of getting today’s date with the time component set to midnight

To actually download the files you’ll have to have another portion of your workflow that gathers the files based on your date range. Since it looks like it’s in datatable format I will give an example on how to filter the datatable based on the date range.

1. Use a SELECT statement to get an array of datarows meeting specific criteria from your datatable. SelectedRows is an array of datarows variable and dt1 is a datatable variable.
   Assign SelectedRows = dt1.Select("[Received/Track] >= #" + startDate + "# And [Received/Track] < #" + endDate + "#")
2. Iterate through the selectedRows variable using a for each statement (change the type to datarow).
   For each row in SelectedRows
   a. Within the for each row, use whatever business logic is needed to download each row. Use the code row.item("ColumnNameHere").ToString to pull out the information from the datatable. Change the “ColumnNameHere” section to be the appropriate column name.