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

Keep Left or Trim right function

DonData
6 - Meteoroid

DonData_0-1590169233855.png

 

in Client Loc Code I need to keep the first 7 characters in the string & drip the "-" as well as the numbers behind it.  I need either a trim right or a keep left function.  I've been using Alteryx for two hours now & I cant seem to wrap my head

8 REPLIES 8
Kenda
16 - Nebula
16 - Nebula

Hey @DonData 

 

If the sting you want to keep will always be 7 characters long, you could use the substring function. It would look like this:

 

Substring([Client Loc Code],0,7)

 

This is saying keep only a substring of the field. Start at the 0 character and keep the first 7 characters. 

 

Otherwise, you could make this dynamic to account for any number of characters are left of the dash. In this case, I could use a RegEx function:

 

REGEX_Replace([Client Loc Code], "(.*)\-.*", "$1")

 

This is saying look for the dash and keep everything that is before it. 

 

The second one is a little more advanced to understand, but let us know if this works for you or you need more explanations. 

DonData
6 - Meteoroid

Hello Kenda,

 

Thanks for the quick response.  It seems to be keeping all the characters in the string still.  The formula makes sense to me though.  It's saying "look in this string and keep characters 0 through 7" right? (not sure if that should be 0 through 6) .  I've made another viz.

 

DonData_0-1590173590689.png

After filter (5) it still kept the dash & everything after it.  If you or anyone could show me what I'm doing wrong it would be greatly appreciated.  Thanks.

Kenda
16 - Nebula
16 - Nebula

Hey @DonData 

 

Sorry for the confusion. Are you wanting to filter your results or change the values?

 

To change the values, you will want to use a Formula tool.

 

If you are wanting to filter out any records that contain a dash, that would require this expression in your Filter tool:

contains([Client Loc Code], "-")

 

 

DonData
6 - Meteoroid

Ah!  Correct. I want to change results like these;

 

TBH0254-03

 

To these;

 

TBH0254

 

So I went to the formula tool.  I am trying;

 

TrimRight([Client Loc Code],"3") & it looks like its working!!  Thanks for the direction!!

DonData
6 - Meteoroid

scratch that.  It has to be keep left.

Kenda
16 - Nebula
16 - Nebula

@DonData  Did you try the substring expression I suggested in an earlier reply?

grazitti_sapna
17 - Castor

Hi @DonData,

 

If you want only the first 7 digits please try and write the below expression in Formula tool.

 

 

 

Substring([Client Loc Code],0,7)

 

 

 

 I can see in the Screenshots that you are using the Filter tool.

Furthermore, since you want only first 7 digits and indexing in Alteryx starts from 0 thus we are initiating our substring from '0' but the length should be 7 only hence the expression.

 

 

                                      substring([column_name,starting_index,length)

 

 

 

I hope this solves your issue.

 

 
 

 

 

Sapna Gupta
DonData
6 - Meteoroid

odd, I replied on Friday.  I guess it didnt take.  in my message on Friday I stated that your first solution had worked I was just using the wrong tool & that my brain had probably checked out already.  I used the REGEX formula.

Labels