Extract file Date from source/database

[SamSurya]

Hi,

I need to build a workflow which would do following things:

1. Extract File date from the source that can be local/shared drive or can be a database where its get uploaded by a team every month on beginning of every month between 1st to 5th.

2. Check an alert if file gets uploaded by the team and execute workflow.

3. It should give notification/alert as "Late" if the file is received after 5th day.

4. Extract Month & Quarter from the date and update a flag as 1 for the current and previous months  and 0 for all coming months and same for Quarter .

5. have to create an half yearly flag separately and should be marked as 1 if all 6 months data is received.

the sample data is below:

Let say we Current Extraction month is April (this is just for assumption We should be able to extract actual month from the file load date)

Name	Month	Quarter	Values	Month Flag	Quarter Flag	Half Yearly
A	Jan	Q1	55,759	Y	1	0
B	Jan	Q1	58,416	Y	1	0
0
A	Feb	Q1	68,328	Y	1	0
B	Feb	Q1	88,853	Y	1	0
A	Mar	Q2	33,014	Y	0	0
C	Mar	Q2	27,733	Y	0	0
B	Apr	Q2	91,650	Y	0	0
C	Apr	Q2	96,848	Y	0	0
A	May	Q2	0	N	0	0
since Current Month is april, so have no values for May and June
B	May	Q2	0	N	0	0
B	Jun	Q2	0	N	0	0

[BrianR · Accepted solution]

Hi @SamSurya, there's a few concepts I can share with you to make your workflow accomplish what you need - bear with me, will be a bit long as you have multiple requirements, I will do my best to share some common approaches to your requirements:

1. Extract File date from the source that can be local/shared drive or can be a database where its get uploaded by a team every month on beginning of every month between 1st to 5th. For this use case, the Directory Tool can extract metadata from any folder you point it to...including local and shared drives - provided that you have rights to that share drive.

2. Check an alert if file gets uploaded by the team and execute workflow. I'm not sure what your product set is, but a common process for checking things such as the existence of a file is the ability to schedule a workflow. One such solution is Alteryx Server - if you have that, you can schedule a "watcher" workflow to run at some frequency...it's sole job would be to check and see if said file exists...if it does, it would produce a "Success" outcome, and then you can use that workflow to kick off another workflow.

3. It should give notification/alert as "Late" if the file is received after 5th day. Using the scheduling mechanism mentioned above, you can create a rule to check existence of file versus any date...this can be 1st of month, 5th of month, 15th of month, etc. I use something similar in my workflows, and I just use a Filter tool to evaluate the date - if this condition is met (i.e., file isn't there after 5 days), the path out of the Filter tool goes to an Email tool, which notifies a recipient or group of recipients that the file hasn't been received.

4. Extract Month & Quarter from the date and update a flag as 1 for the current and previous months  and 0 for all coming months and same for Quarter . Formula Tool - Create new field called Month - DateTimeMonth([DateFieldName]) will give you Month...

For Quarter, a short If-then statement, such as If DateTimeMonth([Date]) in (1,2,3) then "Q1" elseif
DateTimeMonth([Date]) in (4,5,6) then "Q2" elseif
DateTimeMonth([Date]) in (7,8,9) then "Q3" else "Q4" endif

Then for your flag, another If-Then statement, something to the effect of - if DateTimeDiff(DateTimeToday(),[Date],"months") >=0 then 1 else 0 endif

5. have to create an half yearly flag separately and should be marked as 1 if all 6 months data is received. By all 6 months data received, if you mean all 6 files, then you can go back to that original Directory Tool. As mentioned, the metadata of whatever folder you point to is exposed, and this includes DateTime information such as CreationTime, LastAccessTime, LastWriteTime, etc. Using similar techniques from points 3 and 4, you can evaluate if all 6 files are present..such as an if-then, evaluating current date to the Date/Time stamps of the files in the target folder, then count up the files meeting certain criteria, etc. 

Hope you find this helpful, take a look at the documentation articles around functions - I think the Date/Time functions would be helpful for your requirements, as well as the Conditional functions - https://help.alteryx.com/2020.2/Reference/Functions.htm

[SamSurya]

Simply Awesome!!!!

This works for me, Many Many thanks for this.