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

Unique ID on groups of records

cireost
8 - Asteroid

Hi - I am trying to create a unique ID on a group of records. The pattern to the group is separated by empty rows in either Column A or E. Column A always has 5 records but column E is unpredictable. Please see attached input and expected output. Thanks!

11 REPLIES 11
Thableaus
17 - Castor
17 - Castor

Hi @cireost 

 

Wouldn't it be more trustable if we used the first field as a unique identifier - everytime there's a number followed by a dot (Ex: 1. XYZ Tenant), there's a new unique ID.

 

So I'd use RegEX to identify these patterns and Multi-Row formula tool to replicate it.

 

Solution.PNG

 

Cheers,

cireost
8 - Asteroid

@Thableaus Unfortunately the first field doesn't always have a number and therefore, I would like to create my own unique identifier.  would you be able to simply modify the workflow for that?

Thableaus
17 - Castor
17 - Castor

@cireost 

 

Ok, this is a solution considering Column A will always be grouped by 5 Non-Null records.

 

5Records.PNG

 

- Created a Record ID to list a sequence of 1 to 5, considering Nulls to be taking the value from the row before (all based on Column A)

- Created Unique ID considering that an unique ID is added 1 everytime the previous Record ID is 5.

 

Workflow appended.

 

Cheers,

cireost
8 - Asteroid

@Thableaus hi - this is resolved but wanted to tack on a quick question.  In solution 1 that you attached, how do i update the regex formula so that instead of Unique ID being a number, it is the Tenant name being pulled instead.

 

so in Unique ID column,  instead of 1, 2 or 3 it will show XYZ Tenant, LOL Tenant, Random Tenant.

 

 

Thanks!

Thableaus
17 - Castor
17 - Castor

Hi @cireost 

 

Try this in your multi-row formula:

 

IF REGEX_Match([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], ".*\bTenant\b.*") THEN [Row-1:Unique ID] + 1 ELSE [Row-1:Unique ID] ENDIF

 

Basically, if Tenant is found anywhere in your text (it has to be exactly Tenant, it can't be mispelled or anything) the ID is added +1 to the previous Unique ID, else it returns the same previous Unique ID.

 

Cheers,

cireost
8 - Asteroid

@Thableaus 

 

hmm it is still showing up as a number in the unique ID instead of tenant name.  see workflow attached with the new formula.  also, in the real data set, "Tenant" is not always shown.  Is there another way to grab just the text instead?  again, looking for the column to read the tenant names, rather than the numbers.

 

So Unique ID column should look like this:

 

Unique IDGeneral Tenant Information Tenant Name Suite Number Lease Dates Lease Term TenureGeneral Tenant Information Initial Area Building Share %General Tenant Information Lease Period Lease Status Market Leasing Lease TypeRent Details Rate Per Year Amount Per Year Rate Per Month Amount Per Month Rental Value Per YearRent Details Rent Changes On DateRent Details Rent Changes To $/SF-AnnualRent Details Rent Changes To $/SF-Monthly
XYZ Tenant1. XYZ Tenant50000Base0Apr-165.7120.4
XYZ TenantSuite: 1000.25Contract017-May5.68820.4
XYZ Tenant10/1/2014 - 8/31/2019$0.38 (1.5/.5)018-Sep6.16420.43
XYZ Tenant8 Years Office0   
XYZ TenantFreehold  245000   
LOL Tenant2. LOL Tenant112312Base0Apr-164.92660.35
LOL TenantSuite: 2000.25Contract0Jun-1600
LOL Tenant1/1/1999 - 7/31/2021$0.38 (1.5/.5)0Aug-164.6410.33
LOL Tenant20 Years 2 MonthsOffice0Jun-174.78380.33
LOL TenantFreehold  900000Jun-184.92660.34
LOL Tenant    Jun-195.06940.36
LOL Tenant    Jun-205.22410.37
LOL Tenant    Jun-215.37880.38
Random Tenant3. Random Tenant86034Base4.44Oct-195.39070.38
Random TenantSuite: 5000.5Speculative381991Oct-205.49780.38
Random Tenant10/1/2018 - 9/30/2025$0.37 (1.5/.5)0.37Oct-215.60490.39
Random Tenant2 Years Office31833Oct-225.72390.4
Random TenantFreehold  389900   
Thableaus
17 - Castor
17 - Castor

@cireost 

 

IF REGEX_Match([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], ".*\bTenant\b.*") THEN REGEX_Replace([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], "[^a-z]+(.*\bTenant\b).*", "$1") ELSE [Row-1:Unique ID] ENDIF

 

There has to be an identifier for RegEX to capture. So it there isn't a "Tenant", you have to decide what other pattern happens when there's a change in the dataset.


Cheers,

cireost
8 - Asteroid

@Thableaus could the pattern be that we always want the text string (which is the tenant name) that is followed by the numbers?

 

Thableaus
17 - Castor
17 - Castor

@cireost 

 

Try it:

IF REGEX_Match([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], "\d+\.\s+.*") THEN REGEX_Replace([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], "\d+\.\s+(.*)", "$1") ELSE [Row-1:Unique ID] ENDIF

 

 

Pattern followed: Number, "." (dot), Spaces (one or more) then Text

Everything that lies within Text will be used as Unique ID.

Cheers,

Labels