Filtering Dates

[clarrock]

Hi,

How do I filter columns based on today's date. My input data changes daily

Eg : input

Number	2021-05-12	2021-05-13
Sam	300	400

Output

Name	2021-05-12
Sam	300

Is this possible? 

[shreyanshrathod]

@clarrock ,

Use "DYNAMIC SELECT" tool.

Configure it to "Select via a Formula".

In the Formula Pane, use [Name] = Datetimeformat(Datetimenow(),"%Y-%m-%d").

This should resolve your query.

Regards,

Shreyansh Rathod

[shreyanshrathod]

Also, if the data size is not so huge, you can TRANSPOSE the date columns, FILTER out today's date and then CROSSTAB the result.

This would work too.

Regards,

Shreyansh Rathod

[clarrock]

@shreyanshrathod this works but the Name column is missing only the date column outputs - for the dynamic select option

[apathetichell]

transpose date information - then use a filter with datetimetoday() set as the filter (for today) or dateatimeadd(datetimetoday(),-1,"days") for yesterday.

I'm going to point out that there is no reason to mess with dynamic select for this. this is a standard transpose/filter.

[shreyanshrathod]

@clarrock ,

You can always include more field names using OR condition.

E.g. [Name] = Datetimeformat(Datetimenow(),"%Y-%m-%d) OR [Name] = Name.

Regards,

Shreyansh Rathod

[atcodedog05 · Accepted solution]

Hi @clarrock

Using transpose tool and filtering out would a safe bet since it can accommodate more key columns easily.

Input:

Workflow & Output :

As you can see in the above example there is 2 key columns it could n columns in real scenario. Transpose and tool can easily accommodate more key columns.

Then filter name to keep only current date and crosstab (make sure set the same key columns here) it back and it will give you only the current data columns. Refer the output in the above snapshot.

Hope this helps 🙂 Feel free to reach out if you have any questions.