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

Best Practices

Accepted answers

jdunkerley79

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

Attached demo workflow

SummariseJoin.yxmd

All comments

jdunkerley79

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

Attached demo workflow

SummariseJoin.yxmd

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

mjtowne.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703