Dynamic Field Names in Load Script, Dynamic Coloring in Chart, Null Counts

Wed, 22 Dec 2021 15:42:54 GMT

Hello all,

I am currently facing an interesting set of problems that I have found laborious workarounds for. Here's the basic problem statement:

We are measuring the data quality of our warehouse. The basic measurement is the number of NULL values for each [Dimension] in each [Table] we wish to monitor. We'd then like to chart these as simple charts, two values per [Dimension], 'NULL' and 'Not NULL', and a count for each of those, based on a row count for the [Table].

One way I accomplished this was loading each dimension as an if statement, if(isnull([Dimension]),'NULL','Not NULL') AS N_[Dimension]

This works, but I have to manually (via simple excel formula) write all this out for each attribute. Not ideal. The first ask, how do I configure a Loop that does:

For each FieldName in Table

if(isnull(FieldName),'NULL','Not NULL') AS 'N_' & FieldName

Next FieldName

Or something similar to that. That would give me a duplicate set of Dimensions with only Null and Not Null as values, instead of the thousands they currently contain.

The next part of this is charting and coloring this - I will create one chart per Dimension, and we have hundreds over many tables, so this takes a while. The goal is to copy them and change AS LITTLE AS POSSIBLE in each chart to make it work correctly. Currently, I change the Dimension (easy, drag and drop replace), but the color has to be modified. I use a colormix1([Dimension],color1,color2) since it allowed me to double-click the [Dimension] and copy past the name from the title block of the chart. Is there a better way to do this part, too? What about something that automatically pulls the [Dimension] name from another chart value, like title, [Dimension], data, etc?

If there is a better way to get these metrics, including restructuring the data, I'm all ears. Thanks in advance for your help.

topic Dynamic Field Names in Load Script, Dynamic Coloring in Chart, Null Counts in App Development

Dynamic Field Names in Load Script, Dynamic Coloring in Chart, Null Counts