ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community is currently in implementation of the new SSO experience starting at 5pm MDT. PLEASE DO NOT POST ANYTHING. For more information, read the blog.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

hellyars
11 - Bolide

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

hellyars
11 - Bolide

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

Maskell_Rascal
12 - Quasar

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. 

 

Maskell_Rascal_0-1614806488513.png

 

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

 

Thanks!

Phil

hellyars
11 - Bolide

@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

hellyars
11 - Bolide

@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
12 - Quasar

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. 

 

Maskell_Rascal_0-1614809685367.png

 

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. 

Maskell_Rascal_1-1614809951217.png

 

Let me know if this works for you. 

 

Thanks!

Phil

Labels