SQL script into Alteryx Formula tool

[sxa445]

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

[BRRLL99]

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

[FinnCharlton]

Hi @sxa445 , the equivalent of a LAG statement in Alteryx is a multi-row formula tool. To recreate this SQL query, follow these steps:

1. Use the Input data tool to import your revenue_invoice_data table.
2. Use a select tool to check that your datatypes are correct, especially the orderdate field as we need to sort using this field.
3. Use a sort tool to sort your data. You'll first want to sort by fullaccountnum and cin, as these are your partitions. You'll then want to sort by orderdate ascending.
4. Use a multi-row formula tool. You'll want to 'Group By' fullaccountnum and cin. Select the 'Create New Field' option and make it a Date field, and use the formula [Row-1:orderdate]. You'll also want to set the 'Values for rows that don't exist' option to NULL.

Hope this helps!

[sxa445]

Hi @BRRLL99 

Yes, the input and expected output look correct. 

How do I translate that into Alteryx? 

[rzdodson]

@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! :)

[sxa445 · Accepted solution]

All I ended up doing was 

1. Drag and drop a "Formula" tool onto the canvas.
2. Connect the "Input Data" tool to the "Formula" tool.
3. In the "Formula" tool's configuration panel, create a new calculated field for "previous_orderdate" using the following expression:

LAG(orderdate, 1, NULL) OVER (PARTITION BY fullaccountnum, cin ORDER BY orderdate)