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

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.

Screen Shot 2021-03-03 at 3.04.24 PM.png

alteryx_question_insert_row_if_condition_met.yxzp

Help

Expression

Accepted answers

Maskell_Rascal

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

alteryx_question_insert_row_if_condition_met_SOLUTION.yxmd

hellyars

@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.

Screen Shot 2021-03-03 at 4.27.50 PM.png

alteryx_add_rows_reassign_ids.yxzp

All comments

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.

Screen Shot 2021-03-03 at 4.06.17 PM.png

alteryx_question_insert_row_if_condition_met.yxzp

Maskell_Rascal

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

alteryx_question_insert_row_if_condition_met_SOLUTION.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703