I have a dataset (dataset 1) with individual rows as a specific job (with an identifier key) with details including year and country of operations. I have created cards to show the total number of jobs per year. I have another dataset (dataset 2) which shows historical revenue by Year and Country but it is not specific to a job (or identifier key). I am wanting to show revenue per year on the same card as the total number of jobs per year, so we can see the trend between the number of jobs and revenue. I've tried to use Magic ETL to "Join" or "Append" data from both datasets but without any good results. I guess the link or common column between the datasets is YEAR but when I merge these columns together the revenue per year is duplicated for everytime that YEAR is mentioned in dataset 1. For example for the whole year of 2009 in the UK, the revenue was $1000, it can't duplicated if there are more jobs in 2009 and in the UK.
Example:
Dataset 1
Job #123 United Kingdom 2009
Job #234 United Kingdom 2009
Job #345 United States 2010
Job #456 United Kingdom 2010
Dataset 2
United States 2009 $1000
United States 2010 $2000
United Kingdom 2009 $1000
United Kingdom 2010 $3000
How can I combine these datasets together to show Number of Jobs per year (bar chart) + Revenue per Year (Line)?
Thank you