partition Over

[mjtowne]

Hi, I have the below column in my query that i am trying to replicate in Alteryx what is the best solution.

max(bb.Number_Sku) over (ORDER BY bb.DEPTCATG, bb.Number_Sku desc) as CategoryMaxSkuCount

Example Data

DeptCatG    Number_Sku

73_60                    2

73_60                    5

72_80                    2

72_80                   7

The new column i am wanting is to show the max with in the Number_sku. 

Thanks

Martin

[jdunkerley79 · Accepted solution]

Use a summarize tool (to do a group by and sum) then join the result back to the original

Attached demo workflow

[s_pichaipillai]

@mjtowne

there are 2 ways we can do it

1. If you are really dealing with the SQL server you can use the same formula in Formula tool in In-DB tools

@jdunkerley79

2. from his question , i dont see any partition by class (group by), so if @mjtowne need only the MAX for the whole data set then simply Max then append to the row

i added both ways (In-DB and Standard workflow) but its your choice 

[jdunkerley79]

The OVER syntax is like an inline group by partitioning the data