In-DB formulas
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I am trying to duplicate an existing workflow using nothing but In-DB tools, but i am having a hard time figuring out how to use the in-DB formula tool
could someone please help me figure out what i'm doing wrong? here is the formula i'm trying to replicate, but i cant get even the most simple concatenations to work..
if
[PNRLocatorID] = Null()
then
[BATCH_ID]+'|'+[IssueDate]+'|'+[Min_FlightDate]+'|'+[POO_Origin]+'|'+[TrueNDOD]+'|'+[AgencyCode]+'|'+[TotalTktCouponCount]
else
[PNRLocatorID]
endif
- Labels:
- In Database
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
in-db formulas use SQL so you need to use CONCAT('Batch_ID','IssueDate) etc... Haven't used the IF much in In-Db - maybe an CASE WHEN THEN ELSE END syntax? Or just datastream out and in...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Matthew ,
There is difference from Formula tool expression and In-DB Formula tool expression.
In In-DB Formula tool, you should follow SQL expression.
For example, I made the table of how to express the field and value.
Field | Value(String) | |
Alteryx | [] : square blanket ex [Field1] | '' or "" : Single or Double quotation ex "apple" , 'apple' |
SQL | "" : Double quotation ex "Field1" | '' : Single quotation ex 'apple' |
This makes Alteryx users very confused.
And In SQL, we can't use IF function. We can use CASE WHEN function instead of it.
I think the following expression will work well.
CASE
WHEN "PNRLocatorID" IS NULL THEN Concat_WS('|',"BATCH_ID","IssueDate","Min_FlightDate","POO_Origin","TrueNDOD","AgencyCode","TotalTktCouponCount")
ELSE "PNRLocatorID"
END
I tried that expression at MS SQL Server. If your Database is not support some of the functions, it may not work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
inDB uses the SQL of the database engine that you're connecting to. I usually login to the database and write and test the formula there before pasting into Alteryx.
You can see from @AkimasaKajitani 's post - that might work and will work on MS SQL, for other engines the syntax might be different.
