All 50 States to Show up even if not in Data Set

[ang2816]

Hello!

I am trying to work out an apportionment output file for sales that shows all 50 state abbreviations even if not all states had sales in a particular month for that data input.  I tried using the join and union tool but it didn't work.

Thank you!

[PhilipMannering]

Hi @ang2816 

Can you share what you expect the output to look like?

[ang2816]

Hello! 

I hoped I could get it to look something like this, and show all 50 states in the SHIP_TO_STATE column. Ultimately, I would also select the output to create a tab by the Seller Company Tab column from the input file which is why it doesn't need to show up in the output body.

Thanks!

SHIP_TO_STATE	TPP	NONTPP	OTHER	Total
AL
AK
AZ
AR
CA
CO
CT
DE
FL

[PhilipMannering]

Like this?

[ang2816]

This looked great! However, when I generate the output file and select Prepend Prefix to File/Table Name based on the Seller Company Tab Column, only the states that the company made sales in will show up in that tab. Is there a way to have all states show up in every Seller Company Tab? 

[Luke_C · Accepted solution]

Hi @ang2816 

Give this a go:

[ang2816]

Thank you so much!