Partition Over Query in Alteryx

[tjamal1]

How to implement this query in Alteryx 

Select
customerKey
,storeKey as primary_store FROM (
SELECT
customerKey
,storeKey
,sum(salesAmount) as aggsale
,ROW_NUMBER() OVER(PARTITION BY customerKey ORDER BY sum(salesAmount) DESC) as store_rank
FROM sampletable
where salesAmount > 0
and visitKey is not null
and customerKey in (2073,2083)
group by customerKey,storeKey
) A
WHERE store_rank = 1

Expected output 

customerKey	primary_store
2073	14
2083	31

Sample Table

Book1.xlsx

[neilgallen · Accepted solution]

this is quite simple in alteryx. the attached workflow gets you there.

the first three filters are taking care of the 

FROM sampletable
where salesAmount > 0
and visitKey is not null
and customerKey in (2073,2083)

the summarize tool then calculates the sales amount total by customer key and store key. We then sort by those same dimensions and use the sample tool to extract the top row per group, which is where you're going with 

WHERE store_rank = 1

[danilang]

Hi @tjamal1 

Almost identical to @neilgallen's with the customerKey filtering at the end instead of at the beginning

Dan 

[tjamal1]

Thanks for the reply 
It helped 🙂
Have a good day !

[tjamal1]

Thanks for the reply 

Both solution working for me 
It helped 🙂
Have a good day !