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

SQL Pivot functionality

JMoore
8 - Asteroid

Hey Everyone, I have been running into an issue where a SQL Query I am using works as workflow with the pivot command in it but when trying to access the SQL Query Builder tab it can't recognize the Pivot command, but most importantly it throws an error "Error: Action (55): Parse Error at char(1): An operator must be between operands" when trying to use an action tool for setting up the analytic app. Any suggestions on how to handle this error?

 

Portion of SQL code:

 

WITH t1 AS (
SELECT UDFL.*
FROM database.table_udfl UDFL
) --SELECT UDF TABLE and alias as t1
,t2 AS (SELECT * FROM t1 --pivot the data from t1 in t2 table
PIVOT (MAX(UDF_VALUE)
FOR UDF_NAME IN (table of names))

 

4 REPLIES 4
LordNeilLord
15 - Aurora

@JMoore

 

Quick thought on this, why not pivot the data in Alteryx rather than in SQL?

 

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

JMoore
8 - Asteroid

Hi  @LordNeilLord

 

The second half of the data is dependent on this pivot so it can do a left outer join, if I didn't do the pivot I would have to bring in all the data and then attach it to my other subset, this would cause me to pull in millions of rows that are not needed.

JMoore
8 - Asteroid

The root of the issue I found today and it is due to using * after select the * is an operator that it does not allow in action or function icons as it is restricted, any suggestions on how to still use it in SQL in Alteryx? I am trying to re-configure my query so the * can be avoided but running into several issues still.


JMoore
8 - Asteroid

Spoke with Alteryx support and Jerad was super helpful, using two action tools to update the dates in the SQL query with a text replace: "Replace([Destination], "09-MAR-18", DateTimeFormat(DateTimeAdd([#1],-1,'days'),"%d-%b-%y"))"

Labels