Multi-Row Formula tool: comparable SQL code/process?

[mnmemilymnm]

I am the only Alteryx user at my org, so I am trying to reverse engineer an Alteryx workflow into a SQL query. I am an intermediate SQL coder, so there are still a lot of things I'm learning and hope this forum can help me figure out whether I can create code in SQL that is comparable to how the Multi-Row Formula tool applies logic to a dataset.

I work with sales data, so I use the Multi-Row Formula tool to group sales by customer, and then for each customer, look backward in time and identify whether a certain parameter was met in previous sales.

Is it possible to do the same process outside of Alteryx with SQL code only? Does anyone have any general advice for how to do this? 

What I'm thinking is: Let's say I'm working with Table A, and I want to apply the Multi-Row Formula logic on Column 1. So I create a temporary table by duplicating Column 1, renaming it Column 2, and then I join the new temporary table (Column 2) to Table A, just offset by 1 row, like this:

Then I can create a calculated column to test whether the value in Col 1 is equal to Col 2, which is the same as using the Multi-Row formula tool to test whether [Col 1]=[Row-1:Col 1].

Does this intuitively sound like it would work in SQL? If so, how would I group by customer? -- that's the part when I get stuck mentally.

[chukleswk · Accepted solution]

1. Create a temp table You can do a Row Over Partition By in order to assign a row number to each record
   • The "Partition By" will allow you to group it by customer
2. Once the temp table is created you can join the temp table on itself where temp1.row_num = temp2.row_num (+/-) 1
   • here temp1 would be what you named the table and temp2 would be what you named it as again when you joined it back on itself
   • the + / - would depend on whether you want to compare the current record to the next record or the current record to the previous record (respectively)
3. You can then write a case statement to assign a flag to see if the values match

https://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1

[mnmemilymnm]

This is awesome, @chukleswk. Thanks so much for the help!

[chukleswk]

@mnmemilymnm You're welcome! Let me know if you need further help with it.

[Alx_data]

Hi @chukleswk. There's another option in the multi row tool called 'update existing field'. Can you please explain how to do the same using SQL? Any idea