Partition Over Query in Alteryx
- 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
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
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @tjamal1
Almost identical to @neilgallen's with the customerKey filtering at the end instead of at the beginning
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the reply
It helped 🙂
Have a good day !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the reply
Both solution working for me
It helped 🙂
Have a good day !
