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

Changing Data Format

DataAnalyst
7 - Meteor

I have IDs which need to be formatted as per the state code.

 

Below are 3 scenarios.

1.If State Code is 04 then format 123456789 as  123456789

2.If State Code is 05 then format 123-456789

3.If State Code is 06 then format 1234567.89

 

Thank you!

6 REPLIES 6
JohnJPS
15 - Aurora

A formula tool with the following would work:

 

Switch([STATE CODE],"unknown",

"01",ToString([ID NUMBER]),

"02",Left(ToString([ID NUMBER]),3) + "-" +
     Right(ToString([ID NUMBER]),Length(ToString([ID NUMBER]))-3),

"03",Left(ToString([ID NUMBER]),Length(ToString([ID NUMBER]))-2) + "." + 
     Right(ToString([ID NUMBER]),2)

)

This basically does a custom format of your choice for each state code, using the "Switch" statement.... (I have 01/02/03... you would have 04/05/06).  There are many ways to achieve the formatting; this is just one example.  Hope that helps.

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@DataAnalyst

 

I haven't looked at @JohnJPS's solution, but I do have an answer for you.  In my solution I check to see if the ID has 9 digits, if it does, I output formulas for 04, 05, 06 codes.  If another state code is used or the length isn't 9 digits, I output "error".

 

Thanks,

Mark

 

Screen Shot 2016-10-03 at 1.59.20 PM.png

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

@JohnJPS,

 

It's comforting to see similar trains of thought and how unique our solutions can be.  Ideally, we would know which format is the most common and what types of error handling is required for the workflow.  If we can count on a 9-digit value, the modification that I would make to the logic would be to use Left(ID,3)+'-'+Right(ID,6) as an example (not needing to calculate the length of 6).

 

I'm a fan of @jdunkerley79 and his use of regular expressions.  I tend to answer with them too now.  I'm sure that @DataAnalyst will be happy with either of our solutions as they point him in the direction of his request, but also go a bit beyond to his need.

 

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

As @MarqueeCrew mentioned my Regex habits...

 

A Regex look up:

2016-10-03_19-35-57.jpg

Match and then format :)

IIF(REGEX_Match([ID NUMBER],[Match]),
REGEX_Replace([ID NUMBER],[Match],[Replace])
,NULL())
DataAnalyst
7 - Meteor

Thanks MarqueeCrew! It worked. 

DataAnalyst
7 - Meteor

Thanks

 

Labels