Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic Input - Multiple Sheets On One Excel File - Null Fields

Joseph_Mediant
7 - Meteor

Hi Everyone,

 

I'm pretty new to Alteryx and very new to Dynamic Input.  I have one Excel spreadsheet with multiple tabs all using the same layout.  When I try to run it through the dynamic input, if there are any empty (null) fields, I get the schema error.  Is this the correct behavior? 

 

Sometimes certain fields will not have any data.  I have double checked all of the fields in Excel to make sure they are set to the same data format.  If I go in the data and replace all of the null fields in Excel with "0" it works fine.  

 

Is the only option to use a Macro to fix the issue?  

3 REPLIES 3
DataNath
17 - Castor

Hey @Joseph_Mediant, I just quickly mocked something up to investigate this. Here I made 3 Excel sheets with the exact same schema. However, in one of the sheets I removed all records for one field so they became null, much like your scenario. When I went to investigate the metadata of this problem sheet, the 100%-nulled field appears to default to a double (numeric) data type, whereas its populated counterparts in the other sheets are a string.

 

DataNath_1-1663102245081.pngDataNath_2-1663102256309.pngDataNath_3-1663102266902.pngDataNath_4-1663102273868.png

 

Therefore, the schema is actually different due to varying datatypes. Because of this, we can't use the dynamic input tool and we have to move to the usual workaround for this - a batch macro. Here I can force the [Code] field into a string to match the others. Obviously you'll have to change the field etc here to suit your own needs:

 

DataNath_0-1663102221077.png

 

When I run this, the datatype is changed before being output from the macro, and the 3 sheets can therefore be appended to one another as we wish:

 

DataNath_5-1663102318213.png

 

Please let me know if you need any further help or explanation. Hope this helps!

Joseph_Mediant
7 - Meteor

Thanks so much for your assistance on this!

 

Joseph_Mediant
7 - Meteor

Hey @DataNath ,

 

Hope you are still here.  Everything was working great until I added a Credit Hold column on one of the inputs.  I went through the macro you supplied and made sure that the field was set to v_w string so that it was pushing it out correctly to the workflow but I'm getting this error:

 

Joseph_Mediant_0-1671113859436.png

 

I expect that on Record 7 because I have a hidden sheet in the excel file that the other sheets use to pull data for drop downs.  That was happening previously and was not an issue.  Any help you can provide me with here would be awesome.

Labels