input date change

[ntudev]

Hello,

is there a way i can change the date on each of my input datas in one place? every month i have to change the date in the input which has SQL code with the last day of the month and i have to manual change it in each input and if i forget to change one the data will be wrong.

[ShankerV]

Hi @ntudev 

Can I get sample for the input files with fields you are expecting to get modified.

So that I can work on the solution for you and share it.

Shanker V

[gautiergodard]

Hello @ntudev ,

If you would like to dynamically update a date to the last day of the prior month, you could do so using the formula below:

datetimeadd(DateTimeTrim(DateTimeToday(),'lastofmonth'),-1,'month')

 This formula calculates the current month end, and then subtracts 1 month to find the prior month end. Let me know if this isnt what you were looking for!

[ntudev]

so each table has an asofdate which comes from SQL and it needs to be changed to the end of next month  '2022-10-31'

where A.AsOfDate = '2022-09-30'

[gautiergodard]

hey @ntudev 

In that case, I think what you need is this:

datetimetrim(DateTimeAdd([A.AsOfDate],1,'month'),'lastofmonth')

[DataNath]

@ntudev can you not just change your SQL query to be something dynamic? I.e.

WHERE A.AsOfDate = EOMONTH(GETDATE(),1)

This will return the last day of the current month+1 so you won't need to go in and manually edit the Input Data tools again unless this logic/requirement changes.

[ntudev]

i was hoping i would be able to also choose what month i want to run the report on, not just the end of the current month.

[gautiergodard]

@ntudev Ok so for this you would likely need to go the route of an analytic app. You could select the date you want to run the report for in the interface and have this update your workflow accordingly. 

Simple example attached.

Hope this helps.

[DataNath]

Hey @ntudev, if you want to keep the data within the workflow and continue working with it then I'd recommend turning this into a macro rather than an app, which will allow you to update all of the Input Data tools at once. I've put together an example here:

As you can see here, I've set up the WHERE statement as part of the incoming SQL query in both Input Data tools:

We then link our Date interface tool to both (in your case this would be all 4) and find the 'File - Value' section of the configuration. Within there, we click 'Replace a specific string' and reduce this down to solely the date part that we set up in the initial Input Data SQL statement:

The final part is just to Union the two streams together as you currently do, then feed them out of the macro:

Now, in our main workflow, we simply select a date and this updates the queries of each Input Data tool, bringing those in without having to manually go back and edit each of them:

Hope this helps - I can't share this workflow as there's obviously live DB connections etc involved but I believe I've outlined each step needed to recreate this for yourself. Please shout up if you have any issues!