Help with a formula tool to create identify if first string of text matches before _

I'm hoping someone could kindly help me figure out a formula to return the result I want. I have attached by input data and output data so you can see what I'm trying to achieve. I believe it can be done with a formula tool but it might not be as simple as that! I have a string of text in 'SESSION ID' and some of my data has a duplicate 'SESSION ID' BUT it has an underscore after it and some other unique numbers. First of all I'm trying to identify the data where the session ID's match at the start (so if one session id has an underscore in, want the two to match BEFORE the underscore) and if that is a match, then return then the SCSFEE, INVFEE and Comment to the session ID that has the underscore in. So it basically matched these columns with the session ID without the underscore and is the same numbers for the first characters before the underscore. I didn't want to put the amount of characters to match as they can vary sometimes. So as example, session ID have 8 characters at the start say 12345678 and the other session ID is 12345678_202312356 BUT then another row in my data could be 1234 and other session id is 1234_2023123 so I couldn't say match the first 4 characters say as the other example has 8 characters at the start.

I hope that makes sense and I know might sound a little confusing but hopefully the input and output I have attached will make it clear. A massive thank you in advance for your help!

INPUT AND OUTPUT.xlsx

Help

Accepted answers

OllieClarke

@binsell

If you share how you've configured the join, I may be able to help. You need the SCSESSIONID from the Right input (assuming same orientation as my workflow)

All comments

aatalai

@binsell

Followed most of it,so will try the best I can do.

For the check of the SCSFEE (gross) INVFEE (net) being the sme, use the formula tool with the following formula [SCSFEE (gross)]= [INVFEE (net)] This will flag if they are the same or not

I would suggest the multi row formula tool. for the next bit using If isnull([field]) then [field:row-1]) else [field] endif

And that would be one tool per row. I'm not sure why comment for 12345 was not there for the output. Hope this helps

OllieClarke

Hi @binsell here's one way of doing it:

Hope that helps,

Ollie

underscores.yxzp

binsell

@aatalai Thank you for looking so quickly! Well spotted, I forgot to copy over the comment for the 12345 as wanted it there also.

The things is with the SCSFEE and INVFEE, I only want the the same IF the two SESSION ID's match at the start (before the underscore)

Also I haven't used the multi row tool before as I'm brand new to this and not had much training, so trying to figure it out as I go! It might be that you need to replicate for me and attach a work flow (if you of course don't mind!)

I have attached a new file with my correction as mentioned. Once again, thank you for trying to help me with this!

INPUT AND OUTPUT.xlsx

aatalai

@binsell unfortuntely I'm away from alteryx till March so can't upload a work flow.

In regards to only matching then use a filter tool and put the expression in the filter tool, the one I orginally recommended for formula.

Multi row, I would suggest having a go at it, it and see where you can get to, sorry that I can't be more helpful. But do let me know how you get on

binsell

@OllieClarke Thank you so much, wow, that looks intense! As I use Alteryx designer, it won't let me download your workflow. Is there anyone you can kindly put in Designer? Sorry, I'm not sure how different versions work!

binsell

@aatalai No problem, I will have a play around and see if I can figure out in that case. That is how I have got by with most of my work flow! This one I just couldn't seem to figure out. Thanks again

AmitMiller

Hi @binsell, I've attached a workflow that aims to solve your scenario, using the Multi-row Formula Tool

fill missing values.png

as Aatalai suggested, I recommend experimenting a bit with the Multi-row Formula tool =]

An excellent place to start would be the one-tool example! its very comprehensive for this one.
While you'll need 3 copies of this tool (1 for each column), it still presents an elegant solution, to my opinion.

Do note, that you can separate\extract the relevant portion of the sessionID column either via the

Text-to-columns tool (underscore as the delimiter)
Regex tool
1. Tokenize method - which will output 2 new columns (I used the regex [^_]+ which aims to match a sequence of characters that aren't underscore)
2. Parse method - which will output 1 new column per group (here I used the regex (\d+) which aims to find a sequence of digits)
  1. The parenthesis defines a group, and since there's only 1 - only 1 new column would be created
  2. Both regexes will work - just showing 2 ways to approach the same pattern

While Regex usually takes more time to run, it can sometimes shorten other processes

For example - the parse method of the Regex tool extracts only the relevant column

Now - for the Multi-row tool, you'll want to condition if the previous record's session first sequence of digits is identical to the current one - and the current record's value is empty - then insert the value from the previous record.

if
[Row-1:sessionID1]==[sessionID1] and isnull([SCSFEE (gross)])
then
[Row-1:SCSFEE (gross)]
else 
[SCSFEE (gross)]
endif

The same needs to be done for all 3 columns.

Do use the variable references, rather than manually writing them:

variable reference.png

I've only continued the workflow from the text-to-columns as it is the same no matter what starting point you choose

P.S

You could skip the first step of extracting the different session_IDs, and integrate a Regex_replace function as part of each of the Multi-Row tools. However, I think there's a Diminishing return to minimizing the tool count, but making the other tools purpose harder to read

Hope that helps!

Amit.

fill missing values.yxmd

binsell

Thank you for taking the time to look at this! As per my comment to Ollie, I have managed to resolve this and did it only use parse, filter, join and formula tool and worked perfectly! This is also really helpful as gives me exposure to tools that I haven't seen before so really good insight for me!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703