In-DB Unique with Summarize Group By not working

Question

Hi all,

I am trying to remove duplicates in my in-DB data by grouping all the fields with a Summarize In-DB tool. This works if I stream the data out and stream it back in before the Summarize In-DB tool but not if I don't. As in if the pair of tools in the image below are used.

Also, if I stream all the data out and use Alteryx's Unique tool then this also successfully removes duplicates. I was hoping someone might know why this is happening and how I can get the Summarize In-DB tool to remove duplicates without having to stream data out at any point.

My theory is that streaming out changes the data in some way which cleanses it but I haven't been able to confirm this is the case or understand any logical reason for why that would happen.

Any help, much appreciated.

Thanks

sheikhazim · Answer

Yes, the summarize tool in 3 is a copy and paste of 1 so has the exact same configuration. Hence, why I am left baffled by why the output is different.

Thanks

echuong1 · Answer

Methods 1 and 3 should give the same result - is the summarize tool configured the same in both?

sheikhazim · Answer

Hi echuong1,

Thanks for getting back to me on this.

My process is just to compare two tables within a database by unioning them and then remove the duplicates.

I am able to do this successfully using branches 2 (stream out and use Alteryx's unique tool) and 3 (streaming out and streaming back in and using In-DB Summarize). However, I need to do this completely In-DB so I need to use branch 1 which should work but for some reason does not.

The basis of my question is simply, why does branch or method 2 and 3 work but 1 doesn't work? The only difference I can see is that 2 and 3 involve streaming the data out so maybe it is related to this?

When I say a branch "works" I mean the output of 2 and 3 is: each table has 20k records, union to give 40k and then deduplicate to give 20k records which is correct. However, output of 1 is that each table has 20k records, union to give 40k and then deduplicate to give 40k records which is incorrect.

Any help is really appreciated 🙂