Combining data sets

[RaulFinol]

Good morning alteryx community;

I have been trying to solve and issue for the last two weeks and I am stuck. Hoping that someone would be able to shed some light on my issues.

I have 2 data sets. 

The first data set is my company's expense report data. it includes meals, hotel, etc.

Now i am being asked to bring in the full time employee data. When I create the two flows separately my numbers match perfectly, but when i try to combine them into one single output my numbers dont match.

Basically what i would like to do is bring in my entire FTE data which is based on cost center at the lowest level, and i do not want it to repeat if that cost centre booked more than one expense.

I am also trying to add the cost centre hiearchy so that i can do expense and FTE reporting at different levels of the organization

Attached is a picture of my current flow.

Thank you again for the help it is much appreciated.

[Thableaus]

Hi @RaulFinol 

This would need a analysis on data to see what it could be the issue.

I can see the last join you're doing is turning into a cartesian product. You're probably joining on duplicate keys or something like that.

So you can think carefully on aggregating data before joining your datasets. That might be something to investigate.

A proper answer demands us to check your workflow details and probably your data.

Cheers,

[RaulFinol]

Thank you for the prompt reply.

I have tried to create a small sample data set.

Basically i need all of the field from the FTE tab. For example Org unit 11114 is not used in the expense tab but i would need it in my output so that the FTE number is correct.

If you need more data please let me know

[Thableaus · Accepted solution]

@RaulFinol 

I'm not so sure if this is what you want:

Aggregate on Expense Data level (Sum amount of similar accounts) and use left join to get all results you need.

Workflow attached.

Cheers,

[RaulFinol]

Thank you very much;

that is exactly what i am looking for.

Now just have to apply to my larger data set