Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Generate missing rows

Highlighted
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

Highlighted
Alteryx Certified Partner

Hi @shivraj2,

 

Please try the attached workflow.

 

Best,

Vishwa

Highlighted
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!

5 - Atom

Thanks a lot, Vishwa. Really appreciate the help.

 

Here is another solution I achieved through trial and error.

Highlighted
5 - Atom

I found the solution the same way actually!

 

Thanks

Highlighted
13 - Pulsar

Nice!  Can't argue with 2 people agreeing!

Labels