ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community has implemented a new single sign-on experience. If you are having issues with your account, please review the FAQs.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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