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.
Solved! Go to Solution.
https://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1
This is awesome, @chukleswk. Thanks so much for the help!
@mnmemilymnm You're welcome! Let me know if you need further help with it.
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