Help with Row_Number(), Partition By, Order By
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have multiple partition by in TSQL and need help to replicate the same in Alteryx
Scenario 1:
RowNumber ( )
Over
(
Partition by Ln_Id
Order by Acg_Prd_Id DESC
) As RowNum
Scenario 2:
RowNumber ( )
Over
(
Partition by Ln_Id
Order by Acg_Prd_Id DESC,
Ln_Bus_Evnt DESC
) As RowNum
Scenario 2:
RowNumber ( )
Over (
Partition by Ln_Id,
Ln_Actg_Bal_Typ
Order by Acg_Prd_Id DESC,
Ln_Bus_Evnt DESC,
Ln_Actg_Acvy_Id DESC
) As RowNum
Scenario 4:
RowNumber ( )
Over
(
Partition by Ln_Id,
Ln_Actg_Bal_Typ
Order by Acg_Prd_Id DESC
) As RowNum
Thank you very much for spending your valuable time for me.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
hey @Reddy4
If you are using the Alteryx in-db connections, i believe you can achieve this via an in-db summarize tool:
https://help.alteryx.com/20213/designer/summarize-db-tool
This will allow you to group by, and select counts.
Hope this helps,
TheOC
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I cannot use In-DB tools due to gallery connections. Can I know how it can be achieved using Alteryx tools?
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Reddy4
The general methodology to reproduce the Row_number over (Partition Order By) TSQL is to sort by all the fields in both the Partition By and Order By clauses in the order that they appear in the SQL statement and then use a multi-row tool grouped by the fields in the Partition By clause
Make sure to specify the direction in the sort tool for the Order By fields
The attached workflow contains an example for each of the 4 scenarios you included.
Dan
