Insert missing rows based on pattern and then group enties

hellyars

This is a two part question. Sample data attached.

Each entry is currently trapped vertically.

Each entry should have 5 fields associated with it: 1) Name, 2) Hull, 3) Date, 4) URL_PART, and 5) Status.

Typically (not depicted in this data) each hull has 1 entry (consisting of the 5 rows referenced above).

These are the problem entries.

There are two major problems:

1) A given Hull (e.g. AFDB 1) has 3 entries (3 sets of 5 records, this unique feature being Date and Status)

2) Some entries (e.g. AFDB 7) has >5 rows associated with it (AFDB 7 is missing Date and Status).

Questions:

1) How can identify if an entry does not have 5 rows associated with it (using FIELD_NMBR) and insert blank rows to the corresponding missing Field Nmbr/Field?

2) How can I assign each group of 5 records a number 1-N?

grouping_question.yxzp

Transformation

Help

Preparation

Accepted answers

atcodedog05

Hi @hellyars

Here is a logic on how you can get the expected output

Workflow:

1. In the Mutlirow formula tool I am starting a new Hull (Group) ID for every occurrence of Name in column field.

2. Using summarize tool group by Hull (Group) you to get only unique instance of Hull (Group) IDs

3. Using a mapping table with needed fields i am creating combination of 5 fields for each Hull (Group) IDs using append tool

4. Using join tool & union tool i am doing a left join so that all fields which exist and doesnt exist previously gets added up. This can also handle even if for a Hull (Group) ID field is repeated.

5. Using formula tool and same formula as yours to assign FIELD_NMBR

6. Sorting row by Hull (Group) ID & FIELD_NMBR ascending.

Hope this helps 🙂

grouping_question.yxmd

All comments

atcodedog05

Hi @hellyars

Can you provide expected output for reference.

AngelosPachis

Hi @hellyars ,

I used a batch macro to generate all missing rows and a multi-row formula tool to assign each group of 5 records a number per hull

You can notice how some rows are empty, because those were generated in the batch macro.

Hope that helps, let me know if that worked for you.

Cheers,

Angelos

grouping_question_.yxzp

hellyars

@atcodedog05

Hull (Group)	Hull	Field Nmbr
1	AFDB 1	1	ARTISAN
1	AFDB 1	2	AFDB 1
1	AFDB 1	3	06/01/1978
1	AFDB 1	4	URL
1	AFDB 1	5	Disposed of
2	AFDB 1	1	ARTISAN
2	AFDB 1	2	AFDB 1
2	AFDB 1	3	06/15/1974
2	AFDB 1	4	URL
2	AFDB 1	5	Disposed of
3	NCC 1701	1	ENTERPRISE
3	NCC 1701	2	NCC 1701
3	NCC 1701	3	11/5/2345
3	NCC 1701	4	URL
3	NCC 1701	5	Commission

atcodedog05

Hi @hellyars

Here is a logic on how you can get the expected output

Workflow:

1. In the Mutlirow formula tool I am starting a new Hull (Group) ID for every occurrence of Name in column field.

2. Using summarize tool group by Hull (Group) you to get only unique instance of Hull (Group) IDs

3. Using a mapping table with needed fields i am creating combination of 5 fields for each Hull (Group) IDs using append tool

4. Using join tool & union tool i am doing a left join so that all fields which exist and doesnt exist previously gets added up. This can also handle even if for a Hull (Group) ID field is repeated.

5. Using formula tool and same formula as yours to assign FIELD_NMBR

6. Sorting row by Hull (Group) ID & FIELD_NMBR ascending.

Hope this helps 🙂

grouping_question.yxmd

hellyars

@AngelosPachis @atcodedog05 Thank you both.

@atcodedog05 Your solution is elegant, simple, and easy to follow. It is definitely an approach I must commit to my toolbox. I run into this problem a lot (especially when using Alteryx to scrape and parse 1990s vintage HTML sources) Thanks!

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157