Error using Substring in Formula Tool

[Tim_at_Ford]

This is my first post.  Good afternoon all

I was wondering.  Has anyone done an Altreyx substring function that would handle/translate something like this from SQL?

((SUBSTR(<column name>,1,2) IN ( 'A ', 'B ' ,'C ' ,'D ' ,'E ' ,'F ' ,'G ' ,'H ' ,'I ' ,'J ' ,'K ' ,'L ' ,'M ' , 'N ' ,'O ' ,'P ' ,'Q ' ,'R ' ,'S ' ,'T ' ,'U ' ,'V ' ,'W ' ,'X ' ,'Y ' ,'Z '))
OR           (<column name> like '%''%')
OR          ((<column name> like '%L L C' or <column name> like '%Llc')))

I've tried a couple of iterations, the parameter settings for the call are similar but it's

1.  I see something similar to the "IN" in SQL for Altreyx "value in" - does this serve the same function?
2.  I don't see something similar to the  "Like" in SQL for Altreyx

Can some one advise??

Tim

[JohnJPS · Accepted solution]

Hi @Tim_at_Ford

Yes, The first portion of your statement is achieved with:

((SUBSTRING([FieldName],0,2) IN ( 'A ', 'B ' ,'C ' ,'D ' ,'E ' ,'F ' ,'G ' ,'H ' ,'I ' ,'J ' ,'K ' ,'L ' ,'M ' , 'N ' ,'O ' ,'P ' ,'Q ' ,'R ' ,'S ' ,'T ' ,'U ' ,'V ' ,'W ' ,'X ' ,'Y ' ,'Z '))

 Notice that SubString is all spelled out, and starts at 0 vs. SQL which starts at 1... otherwise virtually identical.

But... I'm not sure what the next portion means: 

<column name> like '%''%')

 Could you elucidate that one?

Although the last portion can be achieved with the EndsWith function...

OR ((EndsWith([FieldName],'L L C') or EndsWith([FieldName],'Llc')))

Alteryx also supports RegEx functions which work great for SQL "like" type of logic.

Hope that helps!

 - John

[Tim_at_Ford]

John

Thanks a lot!!  And the other one, the LIKE command, I used Altreyx's "contain" command and I got what I was looking for.

My only other question is how would I represent an apostrophe ( ' ) in the contain lookup.  I am sure, like any other compiler, it would be construed as some kind of escape character that would crash my query.

Tim    

[JohnJPS]

Alteryx is similar to R in that it supports both single and double quotes, so to match a single, just wrap it in doubles, or to match a double, wrap it in singles. The following is valid

IF ([column]="''") OR ([column]='"') THEN