Counting the number of missing and present values in each colums of a dataset

Question

Hi everyone,

I have a table with data that are not all filled (ie there are missing values) :

ID Field1  Field2   Field3    Field4

1      34      -          -               A

2      -         10       2018        B

3      4         -         2015        B

4      2         -          -              A

I'd like to build from that another table that would give me the number of missing values, and the number of filled values for each column. I'd like to have something like that :

Field1    Field2    Field3     Field4

Missing_values       1            3           2              0

Filled_values           3            1           2              4

Does anybody have an idea about how to do that quickly? (In reality I have 25 columns/fields, and I'm wondering there is a quicker solution that doing 25 filters...)

Thableaus · Accepted Answer

Hi @GraceK_dup_114

Using the Data Investigation Tools you can do this kind of analysis really quick.

The Basic Data Profile and the Field Summary tools present you this information with simple configuration.

Cheers,

MarqueeCrew · Accepted Answer

First there is the field summary tool.

Second, you can transpose the data then use a select to change value to .length of 1. Then summarize and group by name and use the appropriate counts.

Cheers,

Mark

Thableaus · Accepted Answer

@GraceK_dup_114

Here's an example with the Basic Data Profile tool.

Using the Cross-Tab Tool you can display your data like you want.

Filter by Nulls and Non-Nulls (statistics) and you have exactly what you're looking for.

WF attached.

Cheers,