Cummulative count

[rprashar]

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

[CharlieS · Accepted solution]

@rprashar

Solution is attached. There's probably a more simple way to accomplish this, but this works.

[JohnJPS]

Hi @rprashar - here's another take.  Enjoy!

 - John

[rprashar · Accepted solution]

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 ?

[rprashar · Accepted solution]

Sorry John using an older version of Alteryx, was not able to see this.

[JohnJPS]

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...

[rprashar · Accepted solution]

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

[CharlieS · Accepted solution]

@rprashar

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.

[rprashar · Accepted solution]

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

[CharlieS]

@rprashar

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.