Creating a RecordID based on a Pattern of Rows....

[celts12289]

Hi Everyone!  I'm new to Alteryx and I'm having a problem processing a file where each record spans multiple rows with no common Identifier to tie them together.  So I'm trying to give them each a RecordID so I can merge them together in one row later.  But I'm having a problem creating the RecordID properly because most of them span 3 rows but there a outliers that only span 2.  But they all of them start with the same Row.  

I was able to do it if the pattern was every three lines but that turned out to be false.  So now I need to base that RecordID on INSRD as the start of a new record.  Any ideas?

[JosephSerpis · Accepted solution]

Hi @celts12289  would something like this help?

[RolandSchubert · Accepted solution]

Hi @celts12289 ,

you can do this using the Multi-Row Formula tool. Calculation rule is, that if [Field_1] = 'INSRD'  then increase RecordId by 1. To calculate the rank, a second Multi-Row Formula tool can be used, using the Group By function. I've attached a sample workflow. Hope this is helpful.

Best,

Roland

[celts12289]

Thank you both!  That worked perfectly!  Loving Alteryx and can't wait to learn more!

[celts12289]

So the Multi Row formula's worked great but for some reason not consistently?  It's the same data I'm running through the workflow each time and sometimes it works and other times it doesn't?  I checked for NULLS in the data but there aren't any.  Any ideas on what may be causing the inconsistency?

[celts12289]

I was able to resolve the inconsistent issue.  See the attached Workflow example.