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

Cut out cerain area form the rows of a column. Which tool?

Marek89
8 - Asteroid

hey guys,

 

just one important question: as you can see on the screenshot, i have a column with segments, which always start with "Custom Segment >". This is the part, which i actually do not need. Now i want to learn different oppurtunities to solve this problem. So Maybe a new colum what includes just the part i need

 

- how Can i delete the part "Custom Segment >"?

- how Can i just select everything what starts with "IQDX..."

 

Thank you so much! :)

 

 

screen.JPG

7 REPLIES 7
jnans
8 - Asteroid

@Marek89  For this you can use the Text to Column tool. This can be found in the parsing tool pallet. Choose the > as your delimiter and then 2 columns, rename what you have to and then use a select tool to get rid of the unwanted columns. You might also have to use a cleanse tool to get rid of the unwanted space of your new column as well.

danilang
19 - Altair
19 - Altair

Hi @Marek89 

 

If you just want to delete the "Custom Segment >"  you can use a formula tool with 

 

 

Replace([FullPath],"Custom Segment >","")

 

 

If you want to select everything from IQDX onwards, use

 

 

Substring([FullPath],Findstring([FullPath],"IQDX"))

 

 

 

If you want the make the changes directly in the FullPath column, specify "FullPath" as the OutputColumn.  If you want the result to appear in a new column set the Output Column to the name of the new column

 

Dan

 

Dan

Marek89
8 - Asteroid

thank you all, everything works :)

 

Can you just shortly explain to me what this formula does: Substring([FullPath],Findstring([FullPath],"IQDX"))

 

Is it looking for a specific expression like "IQDX" in this case and takes everything what follows after that?

 

Thank you so much! :)

jaimonsk
8 - Asteroid

yes.

 

The normal syntax for substring function is  is Substring( [Col Name],start,end)

 

Find string function gets the position where the string "IQDX" starts. So basically what Substring([FullPath],Findstring([FullPath],"IQDX")) would be doing is, getting the position where "IQDX" starts and then extract the rest data

Marek89
8 - Asteroid

hey, thank you.

 

but what does "start" and "end" in this context specially mean, do you have an example?

 

because in this case Substring([FullPath],Findstring([FullPath],"IQDX")) -> "Findstring([Fullpath)] would be "start" and IQDX would mean "end" which does not make sense to me.

 

Thank you very much

danilang
19 - Altair
19 - Altair

Hi @Marek89 

 

This is a example of a nested function. 

In general any time you have a function of the form 

 

Func(param1,param2,...)

 

You can replace any of the params with another function as long the second function returns a type that is compatible with replaced param.  If you replace a string param, the replacing function has to return a string, a number param can be replaced with a function that returns a number

 

As @jaimonsk pointed out, the syntax for substring is 

 

Substring( [Col Name],start,end)

 

What he didn't mention is that "end" is a optional parameter.  You can omit the "end" parameter and the substring function will return the string from the "start" position to the end of the line.  That's the case with your function

 

Substring([FullPath],Findstring([FullPath],"IQDX"))

 

There is no "end" parameter in this statement

 

The inner most part in green replaces the start parameter and is executed first and returns the start position of "IQDX" in the string [FullPath].  Lets say that this position is 12.  Once this done, the outer function executes replacing the Findstring([FullPath],"IQDX") with 12, so the expression becomes 

 

Substring([FullPath],12),  

 

returning the part of [Fullpath] starting at position 12.

 

Dan

Marek89
8 - Asteroid

Hey, great answer! Thank you! :)

Labels