Gracefully Handling Not Present Columns

[brianscott]

Hello gurus and aces - 

I've have this situation:

User is prompted to enter a value. 

Value is sent to dynamic input. 

Results of dynamic input goto a cross tab.  New column is created, aggregating a 1-M dataset.

Results of cross tab are joined back up to output of dynamic input for master data stream. 

Perform a function on results of the cross tab. 

Now, if the user enters a value that returns for the dynamic query , everything is golden and we have saved the day.  But.  If the user enters a value that does *not* return anything from the dynamic query, my operation of performing  a function on the results of the cross tab fails complaining about an unknown variable.  

I'm banging my head against this.  What I really need to do is detect this situation and abort on the primary processing stream, and do something else like tell the user that they're query didn't come back with anything.  But having an error tends to muck everything else up.  I feel like the solution is simple, but I cannot figure it out.  

See attached work flow for a close enough mock up. 

Can anyone help?

Thanks.

brian

[jrgo · Accepted solution]

Hi @brianscott

I've encountered this a lot. Attached is how I usually solve. I'll add a Text Input box with the fields i need, NULL values within them. The select tool that comes after will change the field types to what they should be (come through as Bool since there's nothing in them) and union together.

I didn't add this in, but after it passes the tools that would error out if there were no records, use a Filter tool to filter the record placeholder out of your data set.

Hope this helps!

Jimmy

[brianscott]

Hi  jrgo - 

Your solution is gorgeous.  Thank you! 

brian

[ajr2183]

Is there a way to filter these records out after you pass it through a 'Basic Table' tool? In the unlikely event that there are no records for a particular table I am creating, I want to make sure that the table at least gets created, even if it has no records. If I add these placeholder records, the table will get created, but I don't actually want the records to show. The problem with the basic table tool is that once you pass data in, you cannot access that underlying data anymore to apply filters.

Thanks,

Adam

[jrgo]

Hi @ajr2183,

I wasn't aware that the Table tool behaved this way when it didn't receive any records. To answer your question; no, there's no way to filter the data after the table has been converted to a reporting object.

Attached is an option that you can use to ensure there's always a record to satisfy the Table tool requirement to generate the columns.

Hope this helps!

Jimmy
Teknion Data Solutions

[ajr2183]

Hey jrgo,

Thanks for the reply. I was actually playing around with some other options, and I think I came up with an alternative that is pretty simple. In the Basic Table tool, you can apply row rules. I applied a row rule that basically says, When the Notes column = "DUMMY DATA USED FOR TABLE STRUCTURING", then set the Style of the "Replacement Text" to blank. I think this is essentially doing the same thing you did, just another way to do it.

Thanks again