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

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