Hi there, I have the below workflow which is basically a daily cash file, with "Balances" and "Movements" sheets. The top part of workflow pulls through the movements sheet fine, but I would ideally like the "Set Date" column to also follow the idea of being the previous date when ran. So, has to be a workday IE ran on a Monday it would pull Fridays date.
As I have the previous date header in a container as you can see below also, from onedrive input file automatically that I receive. Would this be useful in my question? Is there a way to get that correct prior day ran date, into my "Movements" sheet for the 6 rows, which would ideally stack below for each day ran? (6 new rows per day ran below each other).
Please let me know if you need any further information or to clarify anything?
Solved! Go to Solution.
Hi, @JSheppard98
Kindly create a mock sample file for the available input data BEFORE the point where you'd like transformation help, AND please include a sample of what the output after transformation should look like.
The community can help if we have a little more clarity 👍
Cheers!
Hi @RobertOdera Hope you are well!
Apologies for this,
I have attached a sample file and a screenshot below!
So I would basically want to each day run my workflow, and then generate a new 6 rows beneath the previous data stacked. Using the previous date (only work days) so the 25ths date rows copied would be ran today, the 24ths date rows would have been ran on the 25th etc.
Tomorrow when running the workflow, I would want new 6 rows pasted beneath the 25th, with date 26th. If that makes sense?
Hi, @JSheppard98
I'm doing great, thanks for asking, and I hope the same for you.
Thanks for clarifying and for the sample data. I think I get it now.
We initialize auto-generated weekday Run_Dates with
IF DateTimeFormat(DateTimeStart(),'%A') != "Saturday" OR
DateTimeFormat(DateTimeStart(),'%A') != "Sunday" THEN
DateTimeStart() //datetimestart gives you the datetime that you run the workflow
ELSE NULL()
ENDIF
We will filter the "Today" date as the first of the auto-generated dates, i.e., Run_Date is Today (auto-generated only if it is a weekday)
Set_Date is the previous date (except if Run_Date is a Monday, then Set_Date is the previous Friday)
IF DateTimeFormat([Run_Date],'%A') = "Monday"
THEN DateTimeAdd([Run_Date],-3,'day')
ELSE DateTimeAdd([Run_Date],-1,'day')
ENDIF
Please see the below.
Please mark it as an acceptable solution if it works for you.
Cheers!
You are most welcome - cheers!