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

[binsell]

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! 

[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

[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! 

[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!

[OllieClarke]

@binsell I'm using designer too :)

If you're on a different version of designer, then this article explains how to open workflows made in more recent versions: https://knowledge.alteryx.com/index/s/article/Adjusting-Alteryx-Files-for-Different-Versions-1583460... 

For now, this is the non-packaged version of my workflow. You'll need to repoint the input tools to your data

Hope that helps,

Ollie

[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 

[binsell]

@OllieClarke Thank you so much, I managed to figure it out and download your work flow you so kindly created! I will give it ago and let you know how I get on. Thanks so much for your help on this!

[OllieClarke]

@binsell no worries :) I have assumed that the non-underscored SCSESSIONIDs are unique. If that's not the case then my workflow might not work, just an FYI

Let me know how you get on!

Ollie