I have a table that stores Primary Occupation for individuals. While most of them have data, some are missing. I would like to extrapolate the missing at summary level.
Example:
IndividualID
Primary Occupation
1
Business
2
Business
3
Employed
4
Missing
5
Business
6
Missing
7
Employed
If I create a Pivot of Profession
Business
3
Employed
2
Missing
2
Total
7
I want to re-distribute Missing between Business and Employed in same proportion as Business and Employed
So New count of Business = Business + Business*Missing/(Total-Missing) = 4.2 rounded to 4
So New count of job = Business + Business*Missing/(Total-Missing) = 2.8 rounded to 3
If you are OK with static values, I would do it during the data load. After you load the initial data, load the counts into a new table, and then join that to your first table to calculate the final counts in yet another new table.
