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

Error using Substring in Formula Tool

Tim_at_Ford
7 - Meteor

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

3 REPLIES 3
JohnJPS
15 - Aurora

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
7 - Meteor

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
15 - Aurora
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
Labels