Join back Summarized columns to horizontal dataset

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

Workflow

Datasets

Accepted answers

Jonathan-Sherman

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

20191031 - JoinBackTable.yxmd

All comments

TonyA

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

JoinBack.yxmd

benakesh

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

Jonathan-Sherman

Hi @csh8428.

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

20191031 - JoinBackTable.yxmd

csh8428

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

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167