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?

[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

[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 · Accepted solution]

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 🙂

[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!