Neophyte question about directionality with the left() and right() functions

[sameersheikh]

Hello Everyone,

I'm working through yet another problem set, when the left() and right() functions threw me into a hole of confusion for the past hour where I've been engaging with ChatGPT to try and explain things to me.

Here's the expression:

if 
left([_CurrentField_],1) = "S"

then
right([_CurrentField_],length([_CurrentField_]) - 2)

else
right([_CurrentField_],length([_CurrentField_]) - 1)

endif

Just some data, for the purposes of me trying to figure out what's going on:

[Unit Cost]

$10.00

[RRP]

S$11.00

So, the expression looks for a leading "S", if it finds it, it's supposed to delete two characters from the LEFT-MOST side.  If it doesn't find an "S", then it deletes only one character from the LEFT-MOST side.

So, you'd end up with

[New_Unit Cost]

10.00

[New_RRP]

11.00

It seems straight-forward enough, but looking at the expression, I was completely dumbfounded.  I looked the the right() function, and was thinking "the heck?  why is right() deleting characters from the LEFT MOST SIDE?"  shouldn't it be left(), instead of right()?  Well, when I changed right( to left(, it ended up deleting the RIGHT-MOST characters, which amplified the confsion.  I got something like this:

[New_Unit Cost]

$10.0

[New_RRP]

S$11.

Keep in mind, that I am not familair with programming, so if this is some fundamental aspect of that (like 0 is really 1, and 1 is really 2), then I'm not priviy to it.

To me, this seems very counter-intuitive, can someone please explain?  I mean bless ChatGPT, but it tends to agree with everything I say, so I'm not really getting a straight answer there.

This is my broken, and not completely settled understanding of the functions, and please correct:  with respect to the -2 option, left() and right() don't mean to start from the left-most or right-most positions, respectively.  Instead, they tell you "direction of movement".  So, right() means that you start from the left-most position and the -2 means you move to the RIGHT 2 characters, and remove everything to the left of that position; similarly, left() means that you start from the right-most position, and the -2 means that you move to the LEFT, 2 characters, and remove everyting to the right of that position.

If this is the case, as I said, it's really counter-untiitive and messy - at least to me. 

Any any all help will be greatly appreciated.

[SPetrie · Accepted solution]

Left and Right indicate the starting points.

Left starts on the left side and goes the indicated number of spaces and Right does the same but starting from the Right side.

You will end up with the specified number of characters starting from the Left or Right position.

Left starts on the Left and then moves Right. Right starts on the Right and moves to the Left.

Hopefully I break these down in a way that helps.

if
left([_CurrentField_],1) = "S"

/* Start on the left of the current field and go 1 character to the Left.  */

then

right([_CurrentField_],length([_CurrentField_]) - 2)

/* If it is an S, then starting on the right of the current field, go the number of characters  equal to the length of the current field but minus 2. This will result in the first 2 characters being chopped off  the left side*/

else

right([_CurrentField_],length([_CurrentField_]) - 1)

/* If its not an "S" then starting from the right, go the number of characters equal to the length of the current field -1, this will result in one character being chopped off  of the left side*/

endif

[sameersheikh]

Thank you for your reply!

if
left([_CurrentField_],1) = "S"

/* Start on the left of the current field and go 1 character to the Left.  */

Start on the left of the current field, and move one character to the left, and if it's an "S," do the following:

then

right([_CurrentField_],length([_CurrentField_]) - 2)

/* If it is an S, then starting on the right of the current field, go the number of characters  equal to the length of the current field but minus 2. This will result in the first 2 characters being chopped off  the left side*/

else

Starting from the right, of the current field, calculate the length of characters in the cell (or whatever it's called in Alteryx) and subtract 2 from it.  Then count each character starting from the right, moving to the left, equal to the figure you got when subracting 2 from the letter count you just did; print/record the new string

right([_CurrentField_],length([_CurrentField_]) - 1)

/* If its not an "S" then starting from the right, go the number of characters equal to the length of the current field -1, this will result in one character being chopped off  of the left side*/

endif

In the event, there's no "S" as the left-most character, starting from the right, of the current field, calculate the length of characters in the cell (or whatever it's called in Alteryx) and subtract 1 from it.  Then count each character starting from the right, moving to the left, equal to the figure you got when subracting 1 from the letter count you just did; print/record the new string

Does this about sum it up?

[SPetrie]

Yup, that sums it up.

Its a bit more confusing in the context given since they are dynamically calculating the number of characters to grab.

[sameersheikh]

Hah!  This stuff is still confusing me.  I'm slow to absorb this.  I just interchanged left() and right() to get what I wanted.  Thank you for your explanation though.  I'll have to read it a couple more times to get it.  Hopefully, I'll get there.

Curious though, is this the only way to do this?  Is there a very simple command that does the whole "delete x characters from the left/right and print what's left" vs doing the left() & length() trick?

[SPetrie]

Glad you were able to get your desired result. You will eventually get there. One day it will just click.

To answer the other question, there are a number of ways you could get the same result.

Substring would also do it. Give it a string and a starting point and just omit the length and it will give  you the rest.

TrimLeft could be used to delete the S and $ from the left hand side

You could use a replace formula to remove S$ or $ from the field as well.

Regex could also be used to grab data that fits the pattern.

Which you use will depend on the data and the desired end result. Some are more greedy than others when removing the unwanted characters and may start to remove stuff you want to keep.