Problem in Identifying Data Types

[dineshp]

Hi,

Alteryx is not recognizing a field with varied character length to be a V_String even though the length is greater than 16. This is leading to padding trailing spaces when writing into a SQL table. Even 'Auto Field' tool is not identifying this data type.

Version: v11.7.4.4

Below examples should be V_String so that trailing spaces are not padded but instead it shows it as String. I can manually convert this to a V_String using Select tool but the length needs to keep changing depending on the refresh period.

Thanks!

[MichalM]

@dineshp 

In 2019.3 the Autofield tool correctly assigns V_String to the field. 

Could you maybe use the Autofield to assign the correct length and then use the Select tool to force V_String over String?

[dineshp]

Hi @MichalM

Thank you for your feedback. I tried this method but 'Select' tool overrides the Data Type and Length even though i changed only type from 'String' to a 'V_String', the length is not dynamically changing.

For example i followed your step with filter on 'SEA' value on DESC field and the autofield determined it's a 'String' with value 3. I changed this to 'V_String' hoping the length would be dynamically fetched from Auto Field tool and change to 17. But, instead it truncated the cell.

Anyway that the length be dynamic and only data type be enforced ?

I don't see that Alteryx is allowing users to control 'Data Type' and 'Size' Independently of each other in Select tool.

Thanks!

[MichalM · Accepted solution]

The select tool will not dynamically change the length but should persist anything that the Autofield identified - the longest string.

In your case, you have a field with mixed width. Autofield is going to identify the longest string (width 17) however will not set the data type to V_String as it should. If you add a Select tool after the Autofield and change the data type from String to V_String, the width of the column should stay unchanged - 17.

Another way to do this would be by wrapping a Select tool into a batch macro and passing the desired width and data type through the control parameter after you obtain it dynamically from the workflow upstream.

[dineshp]

Hi @MichalM 

Current length is 17 but it will not be the case in future refresh period. I expect it to increase to 25-35. If i once save the workflow with 17 and schedule it's going to truncate anything above 17 in future refresh. I'll take a look at batch macro method you have advised else i will default it to have length 99 since i don't see it going over that.

Thanks.