Interactive Chart with Missing Layer

[barnese]

I am currently working on a report using the interactive chart where sometimes a layer might not be present for a certain input.  I would input an account name and the report that is returned to me will tell me the amount of data month to month that the account has transferred.  It is a bar graph with two layers "inbound" and "outbound", but sometimes any given account will only have data transferred in one direction.  If this occurs, after summarizing and crosstab-ing the data the column is missing so the chart will not render (The field "XXX" is not contained in the record).  Is there any way to change this so that if data is missing it just counts the value as 0 and renders the chart?  Ironically when I go to configure chart it looks correct (shows the data that is there) and I would like this to populate to the report even though the second layer is missing.

If there is not a way to do it in the interactive chart options itself, is there some way to work around it and have my workflow check for the columns and if one is missing add it with a 0 value?

Thanks!

[AngelosPachis]

Hi @barnese ,

That's a really interesting question and the only workaround I could think of is the one you suggested, but I'm keen to see if there is a more dynamic way of solving this.

Essentially I used a "Field Info" tool to get the field names of my dataset and check if Outbound is contained in one of them. If not, I would create a column called "Outbound" and feed that to a separate Interactive chart tool

Not sure if that would work in your instance because I've mocked up this dataset. To see how the workflow will react if Outbound column is contained/not contained in the dataset, just alter the connection in the field info tool. For your case, you would only have one stream going in.

Let me know if that worked for you.

Regards,

Angelos

[danilang · Accepted solution]

@barnese 

Here's an easy way to ensure that field(s) exists in your output

Simply add a Text Input tool with the correct column name(s) and no rows.  Use a Select to set the correct field types for each of the defined columns and union this with your incoming data.  Any fields missing from the input will be populated with null() values.  After this, it's up to you if you want change the nulls to 0 or leave them as null.  The Formula tool just passes through the null values but also includes a commented formula to replace nulls with 0

With both columns in the input the result is this

With the outbound column missing you get this

If you choose to replace the null column with zeros, you'll end up with an orange line at Y=0.  I prefer to not have the line at all, because it shows that the outbound information was missing from the input.  With the 0 line there, you have to investigate to see if the data is missing or if it really is all 0

Dan

[barnese]

Thank you!! Works perfectly!