Generate missing rows

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

Preparation

Accepted answers

vishwa_0308

Hi @shivraj2,

Please try the attached workflow.

Best,

Vishwa

Shivraj.yxmd

danrh

Another option:

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!

Generate missing rows.yxmd

All comments

vishwa_0308

Hi @shivraj2,

Please try the attached workflow.

Best,

Vishwa

Shivraj.yxmd

danrh

Another option:

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!

Generate missing rows.yxmd

shivraj2

Thanks a lot, Vishwa. Really appreciate the help.

Here is another solution I achieved through trial and error.

Shivraj 2.yxmd

shivraj2

I found the solution the same way actually!

Thanks

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703