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

Generate missing rows

shivraj2
5 - Atom

Hi All,

 

I'm new to Alteryx and trying to figure out how to solve the below problem.

 

I have a sales dataset with Country and Segment dimensions. But some countries do not have data for all the Segments.

So I need to generate missing segment rows for each country with 0 sales.

 

Usually, I would use Excel pivot table feature "Show items with missing data". But I have a huge dataset that Excel is not able to handle. 

 

As shown in the below screenshot, I need to transform the "Before" table to "After" table.  Thanks in advance for any help.

 

alteryx generate missing rows.PNG

vishwa_0308
11 - Bolide

Hi @shivraj2,

 

Please try the attached workflow.

 

Best,

Vishwa

danrh
13 - Pulsar

Another option:image.png

The Summarize/Append tools are creating every possible combination, and the Join/Union tools end up adding the extra rows.  The Data Cleansing tool at the end subs in 0's for nulls.

 

Hope it helps!

shivraj2
5 - Atom

Thanks a lot, Vishwa. Really appreciate the help.

 

Here is another solution I achieved through trial and error.

shivraj2
5 - Atom

I found the solution the same way actually!

 

Thanks

danrh
13 - Pulsar

Nice!  Can't argue with 2 people agreeing!

mocalvo
7 - Meteor

Genius!  This solved my problem.  Thank you so much!

Labels