Cummulative count
- 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
Hi,
Is there any way in which I can find the week over week cummulative distinct count of the IDs using any of the Alteryx tools. I have attached the scenario below:-
I can do this in SQL by using self join but is there any way of achieving this is Alteryx. Please let me know.
Thanks
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Solution is attached. There's probably a more simple way to accomplish this, but this works.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the solution it worked for me.
Is there any way in which we can do this by conditional self join technique in Alteryx. In SQL we can just write a conditional self join on ID and this problem gets solved. In Alteryx we have append tool which does the cartesian product, but is there any way we can do conditional cross/self join ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry John using an older version of Alteryx, was not able to see this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @rprashar
If you open my file in a text editor, you can modify the version number in the first or second line of the file, in order to get it to run under your version. (This tricks works as long as the workflow doesn't use any fancy new tools, which mine does not).
PS, you mention interest in conditional joins; you can check out the Advanced Join tool in the gallery; there is a write-up with an example here: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Advanced-Join-value-from-one-file-is-between...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @CharlieS ,
Thanks for the help.
But consider the case below:-
The above example is a special case in which some IDs are not there in some weeks. For example geo - a doesn't have wk2, but we are showing it in the output without changing the value of the running count.
So I want these missing weeks in the output, which is not getting solved in this solution, if you can help me with this special case.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The first thing to do is construct a list of all potential outputs. Then merge this with the other results and fill in gaps where necessary. Solution attached.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @CharlieS ,
Thanks for the solution.
However, I am getting an error that there are more than 16 records in the source during appending.
I have 104 weeks and approx 70 geos. I need a Cartesian product of both of them to get all the combinations.
Can you please help me with this error.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
There's a drop-down setting on the Append Fields tool that can be configured to "Allow All Appends(" or "Warn on appends of more than 16 records". The "Error on appends of more than 16 records" is the default setting so users are aware when a data stream size is increasing like that.
![](/skins/images/12A9B4B958288E867BE947DD48612FB8/responsive_peak/images/icon_anonymous_message.png)