create repeating rows but only one column value is incrementing

[HeadBanger]

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!

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

[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

[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')

[AngelosPachis]

Yes that is correct @phottovy , it's a bit frustrating in this instance because you have to find a workaround.

This is also mentioned in Alteryx's guide to Datetime functions, where for DateTimeParse the use of "%W" is not supported.

https://help.alteryx.com/current/designer/datetime-functions

[Tyro_abc · Accepted solution]

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

[HeadBanger]

I do like the approach of solving the Year issue with a helper table and you can configure the 53 week fiscal year there.  Although 2020 isn't a 53 week year.  Next one is in 2023.

Thanks for all the help from everyone on this eve of the new year.  The formulas are such a big help for someone like me. 🙂

Many blessings to each of you.

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