SQL script into Alteryx Formula tool

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

Best Practices

Bug

Data Investigation

Accepted answers

sxa445

All I ended up doing was

Drag and drop a "Formula" tool onto the canvas.
Connect the "Input Data" tool to the "Formula" tool.
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)

All comments

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:

Use the Input data tool to import your revenue_invoice_data table.
Use a select tool to check that your datatypes are correct, especially the orderdate field as we need to sort using this field.
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.
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

All I ended up doing was

Drag and drop a "Formula" tool onto the canvas.
Connect the "Input Data" tool to the "Formula" tool.
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)

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703