How do I complete this SQL code in Alteryx with the formula tool without directly selecting from the table?
SELECT *, LAG(orderdate, 1, NULL) OVER (PARTITION BY fullaccountnum, cin ORDER BY orderdate) AS previous_orderdate FROM table.revenue_invoice_data
Solved! Go to Solution.
is this your Input
Data | Values |
A | 10 |
A | 20 |
A | 30 |
B | 10 |
B | 20 |
B | 30 |
Expected output:
DATA | VALUES |
A | null |
A | 10 |
A | 20 |
B | null |
B | 10 |
B | 20 |
Hi @sxa445 , the equivalent of a LAG statement in Alteryx is a multi-row formula tool. To recreate this SQL query, follow these steps:
Hope this helps!
@sxa445 another option for you if you haven't explored it already is the In-Database palette. It gives users the ability to tap in to your various SQL tables through OLEDB or ODBC connections, assist with writing the SQL query itself, and gives users the capacity to drop their previously written SQL queries in without having to go through the tedious process of translating SQL to Alteryx syntax for the more complicated queries.
It is in between the Transform and Reporting palettes. Happy hunting! :)
All I ended up doing was
LAG(orderdate, 1, NULL) OVER (PARTITION BY fullaccountnum, cin ORDER BY orderdate)