Alteryx Designer Discussions

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

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

SOLVED

How to create null rows grouped by two column?

DigitalAdam
5 - Atom

Hi All,

 

I am trying to create a workflow that is grouped by column 1 & 2, and if there are not 5 rows for each grouping then null rows are added to get the total number of rows for each grouping to five. 

 

I am doing this so I can create a standardized way to work with the data in excel. 

 

Currently, if there are not rows it will just move onto the next grouping (shown below). 

 

I'd like it to be adjusted_artist, adjusted_track with Rows 1,2,3,4,5 and then so on. If there aren't 5 values to fill these 5 rows id like the remaining rows to populate as "null" 

 

Would anyone have any tips on how to accomplish this? 

 

Screen Shot 2021-02-22 at 12.28.12 PM.png

skeen503979
8 - Asteroid

Hi there @DigitalAdam 

 

If I understand what you're wanting correctly, you'll need to use the Data Cleansing tool under the blue Preparation tab.  This will allow you to replace null values with either a blank or a "0".  After this, you can use the Formula tool to replace the blank or "0" with the text you prefer (which sounds like you want to display the actual word "null").

 

Start by identifying the data type per column with the Select tool.  Then add the Data Cleansing tool and select each of your 5 rows.  Check the boxes in the "Replace Nulls" section of the tool configuration (depending on the data type).

 

 

skeen503979_1-1614016483445.png

 

 

skeen503979_0-1614016390658.png

 

mot
9 - Comet

Hi @DigitalAdam,

 

You can use Append Fields tool to create a blank table with 5 rows for each column1-column 2 combination. You can then fill the table using join and union. 

 

Screen Shot 2021-02-22 at 1.20.28 PM.png

I hope this helps. Best!

DigitalAdam
5 - Atom

Wow this worked perfectly! Thank you so much. I hadn't used some of these utilities before as well so I appreciate the introduction to them! 

 

 

Labels