create repeating rows but only one column value is incrementing

I'm trying to create something similiar to partial match using vlookup. I want to generate additional rows showing a value for a fiscal week since the last record.

This is what i currently have

I've attached what I would like to have since it won't let me paste it here.

I've tried to generate rows but I'm not sure how to influence the change based on the Original Retail column. I'm sure its simple but all I can find in the community seems a lot more complicated or not to my application since I'm referencing a non date column.

I also have a table so that it can reference to get the fiscal weeks in between price changes.

Happy New Year!

alteryx generating rows.PNG

Developer

Fuzzy Match

Iterative Macro

Accepted answers

Tyro_abc

Hi @HeadBanger

Can you check if the attached workflow works for you. I used a look up table ( Text Input) which gives me Number of Fiscal Week in a year and then used that info in Generate Rows tool to achieve the same. Please let me know if you have any question on the formula.

Best Regards

Repeating Rows_Arundhuti.yxmd

All comments

phottovy

I'm not 100% sure I'm understanding the question correctly but here is one way of incrementing the "fiscal_year_week" column to match your example table. It's not super efficient but gets the job done.

Repeating Rows.yxmd

AngelosPachis

Hi @HeadBanger ,

Not sure if my solution is the same with that of @phottovy, but I've spent the time to figure this out so it would be a shame not to post it at least.

The tricky part for me was to identify correctly the beginning of each week ; after that generating the rows and getting the correct fiscal year week was pretty much straightforward.

Let me know if you have any question on the workflow or the formulas in it.

Regards,

Angelos

example.yxmd

phottovy

@AngelosPachisyour solution is much more elegant than mine. Mine is more of a brute force approach that doesn't account for 53 week fiscal years. The thing I found interesting is you can use the following to calculate the week number from a date:

DateTimeFormat([Effective_Date], '%Y%W')

But cannot extract the correct date using:

DateTimeParse(ToString([fiscal_year_week]), '%Y%W')

Tyro_abc

Hi @HeadBanger

Best Regards

Repeating Rows_Arundhuti.yxmd

HeadBanger

Thank you Angelos!

The approach is solid and would be necessary if I didn't have a table that I could reference for the Fiscal weeks and when they start.

Really appreciate the time you took to do this.

Happy New Year.

HeadBanger

Thanks for taking the time and knowledge you shared today 🙂

Happy New Year.

HeadBanger

@ AnglelosPachis

I'm trying to use your solution but I see some issues with it.

It seems the change starts a week sooner than it actually happened. Please see below

Also the start of the year changes from year to year, but in retail its generally in Feb. I tried to change that in your formula for date but it actually made things worse. Let me know if you know why this is moving up a week.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703