Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance during the next 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

Formula function for Adding a calculated column - No In database data received

Highlighted
Asteroid

I am trying to use the formula function to add a column for "Item Retail" = Sales/Sales qty

If there is no sales, show "zero" instead of "#DIV/0!"

The data type is "double"

ItemSales Sales QtyItem retail
Apple15003005
Banana340025001.36
Pear00#DIV/0!

 

Here is what I entered in the SQL expression under IDB enviroment...

if "Daily QTY"=0 Then "Item Retail"=0, Else"Item Retail" = Daily POS"/"Daily QTY" Endif

 

but I continue having syntax errors for this formula when running under In database...

Unknow variable "Item Retail"  

 

Did I miss any steps? 

Thank you. 

 

 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Depending upon the syntax of your database (not all SQL is equal):

 

CASE  
  WHEN "Sales Qty" > 0 THEN "Sales" / "Sales Qty"
  ELSE 0
END

Do try to google a CASE statement for your database (e.g. Oracle, SQLServer).

 

https://help.alteryx.com/11.5/index.htm#LockInFormula.htm%3FTocPath%3DIn-Database%7C_____8

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels