topic Re: Join back Summarized columns to horizontal dataset in Alteryx Designer Desktop Discussions

Join back Summarized columns to horizontal dataset

csh8428 — Thu, 31 Oct 2019 15:02:58 GMT

I have a data set that is summarized by month, categorie, and sub-category. I have another data set that is a summary of ONE category by month. I can't figure out how to transpose the summarized data and join it back to the original data set.

In the example I'm trying to join "Sum_VGC Fees" to be a value along with the corresponding amount.

I'm trying to get it to look like this:

Thanks for your help!!

Craig

Re: Join back Summarized columns to horizontal dataset

TonyA — Thu, 31 Oct 2019 15:42:32 GMT

I had a suggestion, but jumped the gun. I missed that your blending with other fields. I'll have something for you in a couple of minutes.

Edit: Here's a solution. Pretty much what @benakesh said...

Re: Join back Summarized columns to horizontal dataset

benakesh — Thu, 31 Oct 2019 15:31:06 GMT

Hi @csh8428 ,

I think union is required here instead of join tool .

1) Create category , sub category using formula tool for 2nd data set and keep column order same as main data .

2) Union with auto config by position

Re: Join back Summarized columns to horizontal dataset

Jonathan-Sherman — Thu, 31 Oct 2019 15:54:48 GMT

Hi @csh8428.

Hopefully this is what you're looking for? It's a union you need rather than a join since the tables need to be stacked vertically rather than horizontally. Assigning the Category before the union will also mean you can use this technique (transpose, select, formula) for more than one category. Creating the category after the join means you can only replace the Null cells with one category value.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

Re: Join back Summarized columns to horizontal dataset

csh8428 — Thu, 31 Oct 2019 16:05:15 GMT

THANKS!!!!! I was playing around with the transpose tool and couldn't get it to output the way I wanted. I always have problems with that tool