cancel
Showing results for 
Search instead for 
Did you mean: 

Adding an identifier based on relative record position

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

EstherB47
Fireball

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!

 

Highlighted
WesFagg
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

BarnesK
Bolide

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
Atom

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