Pulling the Column with Yesterday's Date

Do you have an Excel spreadsheet that contains columns with different dates? Excel is sometimes used as a data dump or a template in some cases where people drop metrics in columns for certain KPI's.


What if you want to pull the most current column with the most recent date only? The recent date meaning yesterday's date since today isn't over yet.

With Alteryx, you can dynamically pull the most current data only, using the Dynamic Select Tool.


Here is an example data set


I found this use case in the Alteryx Discussions page here. The solution was by T_Willins.


With the data set in your workflow, go to the Developer tab and drag the Dynamic Select Tool to your canvas.


In the Configuration pane of the Dynamic Select Tool, click on the Select via a Formula drop-down.



Double-click on Name under the Fields drop-down on the Variables tab. This adds Name to the Expression pane.

The Name is the 'what', or column from your dataset.


Enter the following formula in the Expression Field of the Configuration pane:

[Name] = DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"day"),'%Y-%m-%d')


This formula is telling Alteryx that we want the column with yesterday's date. It does this with the -1 before the "day" in the formula. Now when we click Run, only the column with yesterday's date will be extracted from the data set.



You can also pull the column with the Metric name if you need to filter just a row of this data later in your workflow. Simply add another expression to your Expression field containing an OR statement such as below;


[Name] = 'Metric'

OR

[Name] = DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"day"),'%Y-%m-%d')


Your results will appear as below;



15 views0 comments