Creating a unique identifier

[ksmith4567]

Hi there,

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!

[PeterA1]

Hi @ksmith4567 if you just need to create a truly unique id, we have a formula for creating a UUID: UuidCreate() I took that from here: https://help.alteryx.com/2019.2/Reference/Functions.htm

Another option would be to just use a formula to say [Field 1]+[Field 2]... until you combine the fields into a unique ID

Does this help?

Best,
Peter

[DawnDuong]

hi @ksmith4567 

can you share sample data and expected output? that will make it easier for the community members to understand your use case correctly and hence offer more help.

Dawn.

[ksmith4567]

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

Group	Price	Week
Toys	$200	20200801
Toys	$200	20200802
Toys	$200	20200803
Toys	$200	20200804
Toys	$200	20201001
Toys	$200	20201002
Toys	$350	20201102
Toys	$350	20201103
Toys	$350	20201104
Toys	$350	20201201
Cars	$1,000	20201102
Cars	$1,500	20201103
Cars	$1,500	20201104
Cars	$1,500	20201201

Week	Week Number
20200703	1
20200704	2
20200801	3
20200802	4
20200803	5
20200804	6
20200901	7
20200902	8
20200903	9
20200904	10
20201001	11
20201002	12
20201003	13

[DawnDuong · Accepted solution]

hi @ksmith4567 

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.

[ksmith4567]

Thank you!! So helpful!