Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to change any string into V_WString

jensroy
9 - Comet

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.

8 REPLIES 8
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
jensroy
9 - Comet

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

jdunkerley79
ACE Emeritus
ACE Emeritus

 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.

MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
jdunkerley79
ACE Emeritus
ACE Emeritus

@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.

jensroy
9 - Comet

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

jensroy
9 - Comet

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

Labels