Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Regarding Variable Date requirements.

PiyushJain
8 - Asteroid

Hi Team,

 

I am looking for some automation ,so that every month,workflow run as per schedule & pick the latest month data.

Here latest month means : last month 

For Ex.:

 

This month workflow will be run as per schedule ,and provide me data for April 2021 month.

 

Currently i am manually change the month in bitbucket.

Then run the workflow manually. 

 

Please help.( please let me know ,if more details required).

 

I saw few links about the same,but not getting exactly what i want.

 

 

Please advise.

Regards

Piyush J

9 REPLIES 9
atcodedog05
22 - Nova
22 - Nova

Hi @PiyushJain 

 

Can you share format of the filename used.

apathetichell
18 - Pollux

To second what @atcodedog05 just said - it's fairly straightforward to create a variable date field - but the filename specifics are fairly unique.

 

so datetimenow() will give you today's date and datetimeadd(datetimenow(),-1,"months") will give you a date last month. but turning that into the filename you want and putting it into your workflow are fairly specific.

PiyushJain
8 - Asteroid

Hi All,

 

First of all apologies for the delay in response.

 

Thanks for your reply.

 

Let me try to clarify my issue:

 

The sql code i m using ( via. But bucket ) is as below:

 

Select * from table name 

Where table name.year  in (2021) and table.name .month in ( 4)

 

4 means april 2021.

 

So everytime i need to manually change the month.

 

I want to automate this through alteryx workflow.

 

Please advise .

 

 

Regards 

Piyush Jain

 

 

mceleavey
17 - Castor
17 - Castor

Hi @PiyushJain ,

 

First, if you can connect via the InDB tools you can simply apply an indb filter and you therefore don't need to write any SQL.

 

Failing that, connect a control parameter to your input tool and in your select statement change the where values to XXX,YYY etc (you will need a control parameter per variable).

 

mceleavey_4-1623757131117.png

 

 

mceleavey_0-1623756921733.png

 

Configure your action tool as follows:

 

mceleavey_1-1623756972552.png

 

Repeat this for all variables.

 

Save the macro. Open a new workflow and drag on your macro:

mceleavey_2-1623757018553.png

Now you can determine the dates using any logic you want, and you can then group by these elements and feed them into your control parameters:

 

mceleavey_3-1623757059365.png

 

These elements will dynamically overwrite those elements in the SQL script.

I've attached my example to give you an idea.

 

I hope this helps,

 

M.

 

 

 



Bulien

PiyushJain
8 - Asteroid

Thanka M.

 

Unfortunately i am not using InDb tool.

 

My source setup is totally based on Sql query.

This Sql query is coming from Bitbucket.

 

 

please let me know if you need more details.

 

 

Regards

Piyush Jain

mceleavey
17 - Castor
17 - Castor

@PiyushJain ,

 

then you need what I built above. This will dynamically amend your SQL statement.

 

M.



Bulien

OllieClarke
15 - Aurora
15 - Aurora

Hi @PiyushJain,

 

Even if you're not using In-DB tools you can still edit the SQL in a data input tool, so I don't think you need to go down the batch macro route suggested by @mceleavey 

 

Clicking the 3 dots next table or query and then clicking the SQL Editor box will let you amend the sql so it only brings in the previous month's data

OllieClarke_0-1623833365373.png

 

PiyushJain
8 - Asteroid

Hi Clarke,

 

I tried as per your suggestion 

 

But getting error as below:

 

Error opening select....... : No column returned.

 

The database column i m using are as below.

 

yr for year

 

mm for month.


I tried to make similar query,but getting above error

 

Select * from table.name

Where table.name.mm = mm (DateAdd(mm,-1,Current_Timestamp))

 

I tried to make similar query,but getting above erro

 

 

 

Please advise. 

 

 

PiyushJain
8 - Asteroid

Hi Team, 

 

Please help.

 

 

Regards

Piyush J

Labels