Count string variables - broken down by null and non null

[ELISAMENDES]

Hi Gurus,


I've a list with string values, as an example:
Country   First Name   Last Name   Prefered store   Age
A   Jon   F.   123                                                    18
A   Daniel   F.   125   
B   James   D.                       31
B   Ana   F.   12                                                     25

I would like to group by country and count the variables with null and non null fields.

So contry A will have

Country   Minimum profile by country   First Name   Last Name   Prefered store   Age   Total
A   Null                                     0                       0                       2                      1   3
A   Total                                     2                       2                       2                      2   8
A   % Null                                     0%                       0%                      100%                      50%   38%

How can I count string values? broken down by null and non null.

Thanks
Elisa

[Lynn]

You could try creating expressions in the query to count things. The expression below should return the number of rows where the age is null. Set the aggregation to sum.

Code Select Expand


case
  when [Age] is null then 1
  else 0
end