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

Creating a unique identifier

ksmith4567
6 - Meteoroid

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!

5 REPLIES 5
PeterA1
Alteryx
Alteryx

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

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
6 - Meteoroid

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

 

GroupPriceWeek
Toys$20020200801
Toys$20020200802
Toys$20020200803
Toys$20020200804
Toys$20020201001
Toys$20020201002
Toys$35020201102
Toys$35020201103
Toys$35020201104
Toys$35020201201
Cars$1,00020201102
Cars$1,50020201103
Cars$1,50020201104
Cars$1,50020201201

 

 

WeekWeek Number
202007031
202007042
202008013
202008024
202008035
202008046
202009017
202009028
202009039
2020090410
2020100111
2020100212
2020100313
DawnDuong
13 - Pulsar
13 - Pulsar

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.

EventID.PNG

 

ksmith4567
6 - Meteoroid

Thank you!! So helpful!

Labels