Percentage of blank vs populated fields display

[#1]

Hey guys,

I feel like this should be an easy problem but haven't been able to figure out an elegant solution yet.

Basically I have a table comprised of customer data, first name, middle initial, last name, address etc...

I would like to create a straight table that displays something like:

Field Name	% Populated
First Name	99 %
Middle Initial	78%
Last Name	100%
Address	100%

Where it checks how much of that particular field is missing or null.

Currently I can make it work by the following:

I have this for each field in the script:

if(len(trim([Last Name]))=0,0,1) as LNFlag,

if(len(trim([First Name]))=0,0,1) as FNFlag,

if(len(trim([Middle Initial]))=0,0,1) as MIFlag,etc...

Then in settings->variable overview -> I just define each variable:

FN% = SUM(FNFlag)/count([First Name])

Then I can create a text object and set it equal to FN% and it will display the percentage I am looking for. But is there any way to get it in the format I mentioned above.  I have a feeling it has to do with set analysis but I am not familiar enough with it yet.

Thanks for your help on this!

[swuehl · Accepted solution]

bapperson,

I think you could it by creating a calculated dimension for your fields to show:

=valuelist('First Name','Last Name','Address')

Then as expression, you could use a pick/match combination:

=sum(pick(match(valuelist('First Name','Last Name','Address'),'First Name','Last Name','Address' ),FNFlag,LNFlag,ADFlag) )/count(ID)

where the ID field was added in the load to create a unique line number, e..g using

...

rowno() as ID,

...

I wouldn't count just the fields you are evaluating, in case these fields have NULLs. count() won't take these lines into account then.

Regards,

Stefan

P.S. I played with the system fields $field etc. to create the field names automatically, but haven't succeeded yet.

[#3]

Works perfectly!  Thanks!