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

Book1.xlsx

neilgallen

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

partition query.yxmd