Alteryx Designer Desktop Discussions

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

Separating "Q#YYYY" columns into "Q# YYYY" and removing any columns > 12 months from today

cslagle005
6 - Meteoroid

Hello, 

 

In my sample data set, I'm trying to achieve 2 things:

  1. Add a space between each instance of the pattern "Q#YYYY".  So for example, "Q32023" would become "Q3 2023".
  2. Remove any column that displays a Quarter that is more than 12 months away.

 

I can get step 1 to work with Regex Replace, sort of. I think the challenge is that this file would be pulled monthly, so the particular Q# and YYYY will continue to change as time passes so the method for this must be dynamic. Additionally, the way I'm doing it right now transposes the data and then crosstabs it back, but that's resulting in the data getting messed up in the process (user error, I imagine).

 

I'm not sure how to approach step 2 because it seems like I'd need to possibly convert the "Q3 2023" into a date/time format like "09-30-2023" and then create an IF formula to compare today's date to the column date and remove the column if conditions are met.  Then, format the MM-DD-YYYY back into the previous Q# YYYY format after.

 

Workflow is attached, thanks so much for any insight you can offer!

3 REPLIES 3
binuacs
21 - Polaris

@cslagle005 For your first scenario use the dynamic rename tool to update the heading to replace the '_' with ' '

binuacs_0-1685562639871.png

 

 

For the second scenario can you provide the expected result? I mean which quarters are you expecting in the result?

 

binuacs
21 - Polaris

@cslagle005 Another method of doing this for renaming your headers

binuacs_0-1685562927461.png

 

SPetrie
13 - Pulsar

You can put the regex expression in a dynamic rename as well instead of using the transpose. 

SPetrie_0-1685563478177.png

 

To dynamically remove columns, I like to use a combo of dynamic rename to update the descriptions, and then a dynamic select to kill them off.

You can use the updated field names to do the calculations for what dates to remove.

SPetrie_1-1685563685307.png

SPetrie_2-1685563716944.png

 

 

 

Labels
Top Solution Authors