Alteryx designer Discussions

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

Generate missing rows

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

Highlighted
Atom

Thanks a lot, Vishwa. Really appreciate the help.

 

Here is another solution I achieved through trial and error.

Highlighted
Atom

I found the solution the same way actually!

 

Thanks

Highlighted
Pulsar

Nice!  Can't argue with 2 people agreeing!

Labels