Partition and Order BY

[s0049790]

Hello, I need help on this following code from SQL, how do I convert it in Alteryx?

row_number() over (partition by adjusted_num, effective_date, file_date order by valdate desc) as join_rownum

[messi007]

@s0049790,

Could you share a sample data with what what you expect to have?

It will help us to give you the solution.

Regards,

[RolandSchubert · Accepted solution]

Hi @s0049790 ,

since you unfortunately didn't provide any sample data, I'll try to explain it in a more generic way.

You can rebuild the ROW_NUMBER() function using a SORT tool and an MULTI-ROW FORMULA tool.

The first step is to sort data according to the partition and the order by fields. While the sort order is not important for the partition fields, you should select "Order" for the "order by" field in consideration of the selection in SQL (DESC).

The next step is to define all partition fields as Group By in the Multi-Row Formula tool and calculate the row number by counting starting at "1" . For the first row in each group (i.e. partition) there is no previous row in the same partition (group), so the value for the previous (non existing) row is NULL. This results in a row number "1". For all following rows, 1 is added.

The result should match the result of a row_number function in SQL.

Let me know if it works for you.

Best,

Roland