Insert Blank Row if Condition is Met & Re-Order Tile Sequence Number

[hellyars]

I want to insert a blank row if a condition is met.

Each Tile represents a group rows associated with a record (after a subsequent transpose).  Each Tile should have 7 rows associated with it  -- as exemplified by Tile #28.  Tiles #22, 24, and 26 only have 6 rows (i.e. they are missing a row). 

Looking at Tile #28...   Sequence #3 should always be "USAF."  Sequence #5 should always include "-16" (F-16, YF-16, KF-16, etc.).   Sequence #4 in Tile #28 is "USAF 310 TFTS" (this list he value for Squadron).    Sequence #4 does not exist in Tiles #22, 24, or 28.  If it does not exist, I need to insert a row after Sequence #3 to account for the missing Squadron value. 

[hellyars]

Updated workflow with desired end-state (using Tile #28 as example).

I was able to use a Generate Rows tool to add a 7th row for those tiles that only had 6.   But, they are added as the 7th row.  I need them to become the NEW #4 and re-sequence the old 4-6 to 5-7.   Position is important in a later Dynamic Rename step.  See Desired Outcome in the attached (updated) workflow.

[Maskell_Rascal · Accepted solution]

Hi @hellyars 

See attached workflow for possible solution. Adding a missing row is simple in theory, but requires use of the multirow formula tool and several joins. 

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[hellyars · Accepted solution]

@Maskell_Rascal  I like your approach.  The solution I managed is less elegant.  

I used a Generate Rows tool to add a  row for any Tile that did not contain 7 rows.

This created the requisite number of rows, but the sequence was out of order.

To solve the sequence issue, I assigned the new rows a value of 4 and used a Summary tool + a Formula tool to re-order the Tile_SequenceNum for those Tile rows that started with 6 instead of 7 rows.  

[hellyars]

@Maskell_Rascal  There is one potential challenge when I try to apply this approach to the wider dataset.  The End-User value might not always be USAF.  It could be Belgium, IDF, etc.  How might I modify that initial MultiRow Tool?

[Maskell_Rascal]

Hey @hellyars 

Here is a solution that isn't dependent on a field having a specific value. It instead uses the Sample tool to split the data. I can then renumber everything and join it back together. 

Btw - if you're going to crosstab your data at the end, you actually don't need to have row 4 present for all records. You just need to have all the records numbered correctly and at least one record has to contain row 4. 

Kinda like the below where I got rid of the row generator and Join/Union tools, but I still get the same outcome. 

Let me know if this works for you. 

Thanks!

Phil