Changing field metadata to align before Dynamic Input

[HSuffolk]

Hi all

I am attempting to input multiple worksheets of data within an Excel spreadsheet - unfortunately these are exports from a system which doesn't let you collate them into one worksheet!

Each of these worksheets follow the same field format, however when they are collating using a Dynamic Input tool, it becomes clear that Alteryx doesn't view them as the same data format. An example would be where the same field in two different worksheets is viewed as a Double in one, and a string in another. A different example would be dates - in one dataset, the date field is correctly empty, and in another, it is correctly populated. Alteryx views the empty field as a string, and the populated field as a Date.

The instinctive solution to me is to find a way to default the field types before input, and then amend accordingly using a select tool later in the workflow. The goal of this is simply to bring in all of the datasets, and then manage the data types appropriately afterwards.

Thanks in advance for any help.

Harry

[TheOC]

hey @HSuffolk 

This thread might be of some assistance:
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamically-Change-A-Field-Type/td-p/3...

Its not exactly the same, but does show how to dynamically change the field types based on information. 

Cheers,
TheOC

[HSuffolk]

Hi @TheOC

Thanks for your response.

That looks really useful, thank you. I can't think of a way for it to solve my current problem - do you have any thoughts on alternative methods instead?

Thanks

H

[csmith11]

@HSuffolk

It sounds like you want to simply add a select tool right after the dynamic input tool to assign the appropriate data types for each field.

If you want to share an example workflow with some dummy data and explain the issue a little more, I'd be more than happy to take a look at it.

Are the field names all the same between each sheet? How about the number of columns, are they the same between each sheet?

[HSuffolk]

Here's an example. All field names, number of columns, every visual element of the schema is the same throughout the sheets. In this instance, Type (1),(2),(3) are each different sheets. A dynamic input would be happy with Type (1) and (3) in a union because each of the data types are the same, but it wouldn't be happy with (2) because it differs.

This is the issue I have, but I have around 20 sheets - I want to figure out a solution that bypasses this restriction with the dynamic input tool.

Thanks

H

[csmith11]

The best solution is going to be using a Macro to replace your Dynamic input tool completely. Unfortunately the dynamic Input tool is too rigid to do what you need.

I've attached here a workflow package that should do what you want.

The Macro: (Goes inside main workflow)

The multi Field Formula tool handles the Field conversion you are asking for. (It may or may not be needed) Please try it without to verify for your use case. You should be able to delete and connect around without any issues.

The Main Workflow:

Please let me know if you'd like a more in depth explanation