We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

General Discussions

Discuss any topics that are not product-specific here.

Using the Wildcard newb help

Lewis_Data
5 - Atom

Hi,

 

Extremely new to Alteryx so sorry if this is on the wrong forum!

 

I have an issue whereby when I look to stack 3 files on-top of each other using the wildcard option it is only bringing back the first file in my folder location.

 

The output table suggests that all 3 files are being read but then only records from 1 of the files (8408 total) is being returned. I have tried with all 3 files and removed the wildcard; all files import successfully in isolation, but not together.

 

Any guidance would be much appreciated!

 

 

 

Michael

4 REPLIES 4
DataNath
16 - Nebula

@Lewis_Data welcome! For issues specific to Alteryx Designer then the best place to put queries like this is in the Designer-specific forum here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/bd-p/designer-discussions.

 

For this issue, are you able to provide a little more context here, perhaps with some screenshots - particularly if there are errors/warnings - or sharing the workflow/files (maybe with mocked data if it's sensitive)? You'll need to ensure that all 3 files follow the same schema i.e. identical field names, order and data types. When using a wildcard, does your Input Data tool path look something like this?

 

DataNath_0-1665668957932.png

 

Running this imports all 4 regional files I have in this folder:

 

DataNath_1-1665668995000.png

 

And all of them get brought into Alteryx - have included file names in the output to demonstrate:

 

DataNath_2-1665669025496.png

Lewis_Data
5 - Atom

Thanks Nathan, I have anonymised the 3 files and attached below. Also included print screen of the process I have followed.

 

In the final upload 1 & 3 dropped in but file 2 is not doing so. I have attempted to format all columns the same but still no look. The reports out straight out of a system and I would prefer not to touch before collating!

 

Filees.PNG

Sheet Selection.PNG

Input.PNG

   

 

Appreciate your support.

Michael

DataNath
16 - Nebula

Thanks a lot for that @Lewis_Data - really useful! From a quick investigation it looks like the error is coming from some of your data being completely null, which Alteryx defaults to double - more of an explanation on a recent case of this I noticed here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-Multiple-Sheets-On-One-E...

 

I included the attached 'Field info' workflow so you can see for yourself - although the names and order are all the same, the data type changes throughout which also constitutes a schema change which will cause an error if you try and use a wildcard. So what's the fix here? Well, whenever we run into different schemas, the play is to use a batch macro instead. Much like in the fix I linked above, here we just force all fields into a standard format (strings in this example) - if you do a bit more investigating and find it's only a few offending fields causing this then you only need to change them, but to be safe with the limited time I have, I just made every single field a string:

 

DataNath_0-1665695480183.png

 

That way, when they come out of the batch macro they can all union just fine.

In terms of the outer workflow itself, I've just fed the control parameter the fullpath of each file, which I have retrieved from the Directory tool where I saved them:

 

DataNath_1-1665695541881.png

 

Now when I run the workflow, Alteryx can happily bring in all 3 files and stack them as we wish. The only thing we need to do now (annoyingly, hence why I mentioned trying to find only offending fields earlier, so that you don't have to do this more than you need to) is change all of the fields back to the data type they should be when they come out of the batch macro - just use the select afterwards to do so:

 

DataNath_2-1665695631374.png

 

Really hope this helps. Please let us know if you need more assistance - have attached everything mentioned here so you can use it as a reference/use the macro.

 

In the macro, you'll also just need to change the Input Data location etc to your own:

 

DataNath_3-1665695699658.png

 

As well as the filepath part of the Input Data string within the Action tool:

DataNath_4-1665695721562.png

 

Lewis_Data
5 - Atom

Hi Nath, sorry for the delayed reply! This project has been on the backburner until now.

 

I have pulled the batch macro in as you have advised but the batch is now only reading the first of the 28 files I need to stack due to a 'schema error'. I'm not sure if I've pulled the batch macro together correctly or not so the files stack?

 

Thanks!

 

 

Labels