Convert Blob Fields to Alpha Numeric

[stapuff]

When doing a sql call via ODBC connection to a table I get KEY fields returned in Alteryx like this:

DB_KEY
16 Bytes

I have to convert the field in the Input Tool via sql [using to_alphanum(DB_KEY) as DB_KEY,] for Alteryx to show the value in the Browse Tool which returns this (what I want to see)

DB_KEY
005056B8435A1EDBA8AAEAC7C77BA0F2

I looked at the Convert Blob tool, but that requires you know the fields and only 1 at a time. I have tables that may have 1,2 or even 10 of these KEY fields.

I looked at Transpose tool however the Transpose tool automatically drops BLOB data types.

I also looked at Multifield formula tool and Blob is not an option there either.

Any thoughts on how to convert these KEY fields to Text would be greatly appreciated. 

[jrgo]

Hi @stapuff 

There's not a straight answer to your question as a blob can represent virtually anything and how it was prepared into a blob will determine how it can be converted back to it's original state, if at all. You may need to ask the owner of the table you're querying either how the blob was prepared or what's required to convert back. With that information, we'd be able to assist you further.

Also, the Transpose tool will work against BLOB field types, but will have issues if you have non-blob fields also selected to transpose. i.e., if the first value it transposed was a BLOB, the result will drop any non-BLOB fields or vice versa.

[stapuff]

@jrgo 

I appreciate the response. The tables are coming from SAP TM Module. Joining on unconverted fields works fine so I think SAP would not be overly concerned on my needs to visualize the values.

Using Transpose... would I not need to select all of the blob fields and deselect the non?  Is there a method/tool that would allow the selection of the data type?

Puff

[jrgo · Accepted solution]

Dynamic Select tool (under Developer) can be used only pass fields with a specific data type.

[stapuff]

@jrgo 

LOL... I found it and just tested it when I seen your response pop up. Thought to myself... Here come the "Dynamic Select" suggestion.