community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How to change any string into V_WString

Alteryx Partner

Hello,

 

I am looking for a way to create a macro that converts any string field into a V_WString.
First I will use auto field to convert all fields into the smallest size.  Then I will convert all strings that are not V_WString into V_WSting.

Why:

Auto field converts this "MAATALOUSYHTYMÄ ISTOLAHTI MARJA, ISTOLAHTI MIKKO, ISTOLAHTI PÄIVI JA ISTOLAHTI TAPIO" into a V_String with length 84. And yes, counting characeters of this string is indeed 84.

When I use Alteryx to write this data to SAP HANA then I get an error saying "Value too large for column". It will not fit into a column of 84 in width.
If I output to SAP HANA using V_String, the column is created as a VARCHAR. If I output as V_WString then the column is created as NVARCHAR2.
The value in question takes up 86 characters as VARCHAR but only takes up 84 characters as NVARCHAR2.

So I wish to convert any string type into V_WString to make sure we have one way that will always work.

Alteryx Certified Partner
Alteryx Certified Partner
One way might be to use a multi field formula. By selecting all strings fields you can use the current field as the formula and change the data type on exit.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Partner

It almost worked!

 

If  only I did not have to define the column size as well (each field will get the same column value).

 

Thanks though, it was a great suggestion

 That was my first thought but preserving the size is hard! If not an issue the use @MarqueeCrew approach. If however you want to preserve size and V_ ness then I have attached a macro and batch macro which should do what you need.

 

It gets the incoming field type and filters to non-unicode strings. It then converts these in a dynamically constructed select within the batch macro. This results in preservation of all the sizes.

 

The main catch in this version is column order is not preserved. I can enhance this to do that if needed.

Alteryx Certified Partner
Alteryx Certified Partner

Hey @jensroy,

 

Do you have CReW macros?  I may have over-engineered this solution, but here's a picture:

 

capture.jpg

 

  • TOP:  Get 1st record, then skip it.  What I'm doing here is getting the output field order to be exactly the same as the incoming data.
  • BOTTOM:  I am finding all STRING fields.
    • Top:  I am setting up a rename for all incoming STRING fields to become "XX_" + Name.  I will later De-Select all original STRING Fields.
    • Bottom:  I am creating a formula to name all string fields without the "XX_" and changing the type to V_WString without changing the length.
  • MIDDLE:  I am renaming strings and applying the dynamic formula to create the original named fields as V_WString.

Then I union the format from the top with the data and it works.  As I said, it might be over-engineered but it works.  I am not packaging the workflow.  I will place the workflow as is into the post.  

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner
Alteryx Certified Partner

@jdunkerley79,

 

Do you like my non-macro approach (uses a CReW macro)?

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted

@MarqueeCrew Oooh dynamic formula construction - very nice :)

 

Gist of it is similar but I built a dynamic select tool and it builds a dynamic formula tool.

Alteryx Partner

Awesome, this worked well! I will dive in to in more on monday to see what is going on under the hood.

Alteryx Partner

Very clever! This is my first time to see/use the "update raw xml". Thanks for showing me this :)

Labels