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

[GraceK_dup_114]

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 solution]

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 solution]

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

[GraceK_dup_114]

What do you mean exactly by "select to change value to length 1"?

[GraceK_dup_114]

Yes, the Data Investigation tool give me the percentage of missing values, then I'd have to do another step for getting the counts instead of percentages, It works...

[Thableaus · Accepted solution]

@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,

[MarqueeCrew]

By changing the length of the value field to 1, you save space. You don't care about the real value, just the non empty value. So why carry the extra data?