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

create repeating rows but only one column value is incrementing

HeadBanger
8 - Asteroid

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

 

HeadBanger_0-1609352076740.png

 

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!

 

 

 
 

 

 

 

 

14 REPLIES 14
phottovy
13 - Pulsar
13 - Pulsar

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
16 - Nebula

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.

 

AngelosPachis_0-1609356877586.png

 

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
13 - Pulsar
13 - Pulsar

@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
16 - Nebula

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
11 - Bolide

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. 

arundhuti726_0-1609363588198.png

 

Best Regards

 

HeadBanger
8 - Asteroid

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
8 - Asteroid

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
8 - Asteroid

Thanks for taking the time and knowledge you shared today 🙂

 

Happy New Year.

HeadBanger
8 - Asteroid

@ 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

HeadBanger_0-1609374933643.png

 

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.

Labels