This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have data in excel that is data shown by week. I am trying to use alteryx to create a field for each unique event in the data. A unique event is defined by being within a certain event group and price point within that group (two separate columns in excel), and then not falling into a consecutive week from the previous same row (i.e. Group 1 at $200 in weeks 2-5 are one event but then if the next row is in week 7, it would be a different event).
Does anyone know what the easiest way to do this would be?
Thanks All! I am including a sample of the data that I have in excel. I want to create a unique ID for different events that take place within a certain group, at a certain price point, and in consecutive weeks. So for example, one event in the data would by Toys $200 weeks 20200801-20200804 but a separate event would be Toys $200 weeks 20201001-20201002 as this is not occurring in weeks consecutive to 20200804.
In excel, I was able to do this by first creating a "column D" with the first row being 1 and then below rows being an "if" statement of whether the groups and price were the same and the week equaled the week of the row above it +1. If it was true it equaled 0. If it was false it equaled 1.
I then created another column that had the first row value as 1 and every row below being an if statement of if "column D" is equal to 0, equal the row above, otherwise, equal the row above plus one.
I have used the Join tool to assign numbers to the different weeks to be able to determine if they are consecutive (see other table below for sample of this data).
You probably can use something like the attached workflow. I include a screenshot of the results here.
Please note that it is best practice to use "Week number" rather than the long-form "Week" field. This can be easily modified once you have the complete list of Week --> Week number conversion. I did not use the Week number table because that table is incomplete i.e. missing November.