How to create null rows grouped by two column?

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

Workflow

Datasets

Developer

Help

Accepted answers

mot

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!

bygroup_equalrows.yxmd

All comments

skeen503979

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

mot

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!

bygroup_equalrows.yxmd

DigitalAdam

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703