Hi All, I have the following two excel files to reconcile File 1 : File’s name System A 20210913 where 20210913 is date formatted as YYYYMMDD File 2: File’s name is System B 13092021 where 13092021 is date formatted as DDMMYYYY I would like to set up a scheduler for my workflow to run automatically following the rules below: * If dates from the two files are the same (13 Sep 2021 in this example), the workflow runs as normal. It will send out the reconciliation result via email to relevant recipients * If the dates from two files are different, the workflow will stop running and will send an email to notify recipients of the inconsistent dates Any idea is much appreciated! Thanks System A 20210913.xlsx System B 13092021.xlsx

Notify when file's dates are consistent

anhdo92

Hi All,

I have the following two excel files to reconcile

File 1 :

File’s name System A 20210913 where 20210913 is date formatted as YYYYMMDD

File 2:
File’s name is System B 13092021 where 13092021 is date formatted as DDMMYYYY

I would like to set up a scheduler for my workflow to run automatically following the rules below:

If dates from the two files are the same (13 Sep 2021 in this example), the workflow runs as normal. It will send out the reconciliation result via email to relevant recipients
If the dates from two files are different, the workflow will stop running and will send an email to notify recipients of the inconsistent dates

Any idea is much appreciated!

Thanks

System A 20210913.xlsx

System B 13092021.xlsx

Workflow

Datasets

Developer Tools

Tips and Tricks

Date Time

Transformation

Accepted answers

atcodedog05

Hi @anhdo92

Here is how you can do it. With help of message tool.

Workflow:

Make sure you configure event to send email on after run with errors.

Hope this helps : )

Notify.yxmd

All comments

mst3k

Use DateTime tools from Parse tool to turn them into consistent date formats

Use a Test tool from the Developer menu to test if the dates are equal, and throw an error if they are not equal. Click the background canvas of your workflow, and in the configuration on the Runtime tab choose "Cancel Running Workflow on Error" - the Test tool will generate an error and that will kill the workflow if the dates are not equal. Then in the Events tab, add a Send Email event when the workflow generates an error

atcodedog05

Hi @anhdo92

Here is how you can do it.

Workflow:

1. In input tool extarct filename.

2. Using formula extract date.

3. In Join tool join on date. If Date is same then go to success block and its not then got to failure block.

Hope this helps : )

Notify.yxmd

anhdo92

Thanks you two,

@atcodedog05 there is no record coming through the Failure path and hence the workflow cannot continue to run and create text to be sent via email. Could you please advise ?

atcodedog05

Hi @anhdo92

That is the required logic. Let say if the date is not same then failure block will have rows which you can use to send the email.

If they are the same failure block will not generate output.

anhdo92

@atcodedog05 I also notice the Join tool doesn't work as expected as it triggers the workflow to run even when dates are not consistent.

What I want to do is
- If dates are the same, workflow continues to run as usual
- If dates are not consistent, the whole workflow will stop and will create an email to notify users

anhdo92

@atcodedog05
Thank you for the reply. Yes following the failure path, I add the report and email tool to create notification email but no email got sent...

atcodedog05

Hi @anhdo92

Here is how you can do it. With help of message tool.

Workflow:

Make sure you configure event to send email on after run with errors.

Hope this helps : )

Notify.yxmd

atcodedog05

Hi @anhdo92

Did you make sure dates were not the same for the above scenario which you tried.

anhdo92

Thanks a lot @atcodedog05 !!!

Another thing I've just come across is the date format. Below is my actual file name

I just wanted to extract the date but looks like the formula doesn't work ...

It creates null value instead of dates...

atcodedog05

Hi @anhdo92

Add ".*" in the formula like highlighted below. I have attached workflow also for reference.

Hope this helps : )

Notify.yxmd

anhdo92

@atcodedog05
I don't know what happened but I just deleted the formula and replicated all the steps once again and it worked! lol

Thank you so much for the help!! 🙂 Have a good day!

atcodedog05

Happy to help : ) @anhdo92

If my response helps please don't forget to mark it as solution.

Cheers and have a nice day!

anhdo92

Hi @atcodedog05

I revisit the workflow today and found an issue.
When dates from two input files are inconsistent, it's easy to notify

However, when dates are consistent, I expect to see data coming out of the Join anchor. Unfortunately no data was found. Also, after the join, is there anyway to separate data from the two files as I need to do some transformation separately before reconciling the two

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703