Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
Works perfectly! Thanks!