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

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?

Workflow

Datasets

Accepted answers

JosephSerpis

Hi @celts12289 would something like this help?

RecordID.yxmd

RolandSchubert

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

RowId.yxmd

All comments

JosephSerpis

Hi @celts12289 would something like this help?

RecordID.yxmd

RolandSchubert

Hi @celts12289 ,

Best,

Roland

RowId.yxmd

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.

Multi-row example.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703