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.
SOLVED

Adding an identifier based on relative record position

WesFagg
5 - Atom

Hey all,

 

I have encountered a speed bump in creating a repeatable workflow and need some help.  

 

The goal is to be able to run this on a weekly basis, and for the workflow to update based on the user's input of a Fiscal Year and Fiscal Week.  This fiscal week will be the target week, but I need to be able to identify the 52 prior weeks as "This year", the 52 before that as "Last year", and so on and so forth.  There will be other inputs and analysis that will be joined with this information, but that will be the next step after I find a solution to this problem.

 

I've created a workflow that identifies the fiscal week that is my "Target", but I need to figure out how to append an identifier to records based on the target week that the user input.  

 

I'm attaching a sample workflow of what I have so far.  I hit a roadblock at the sort tool.  I have a "Target" field that is null other than for my target week.  My plan was to use this fact in the Multi Row Formula tool to identify the 52 records above at TY, etc, but I've realized this method will not work as it's set up.  

 

Has anyone encountered a similar problem and found a solution?  Any and all advice would be greatly appreciated!

 

Best,

 

Wes

4 REPLIES 4
estherb47
15 - Aurora
15 - Aurora

Hi @WesFagg. Would you be able to copy the DT_D extract file into a text input? Can't run your workflow without the data. Thank you!

 

WesFagg
5 - Atom

Hey Esther,  

 

Sorry about that!  I've attached an updated workflow to this message with the data in a text input. Thank you for taking a look.

 

Wes

Kenda
16 - Nebula
16 - Nebula

I added on to the workflow you provided after your Union tool. My approach was to get rid of the years after the user's selected date (because, as I understand, you only want the prior years). After that, I append a field to each row that just has the user's selected week value. Then I use the Multi-Row Formula to add a number each time the user's week equals that row's fiscal week (indicating a new year). 1 will correspond to this year, 2 is prior year, etc. Hopefully this helps!

WesFagg
5 - Atom

Thank you BarnesK!! This is a great solution to the problem, and one that I'll keep in mind for future problems.  

Labels