1 Reply Latest reply on Nov 27, 2016 8:25 PM by Nachiket Dekhane

# Joint or Union data set

Hi.  This is my 1st forum msg.  I understand the concept of Joint and Union data set.  But I do not know which method work best for my situation.

I have 5 years of data set on participation of an event, each year in 1 excel file.  The event has 10 similar set of activities every year.

Every year there was abt 2000+ participants who may be new or repeat participants.  I am keen to track those participants who come for all 5 years via their ID.

So I did an inner joint of 5 files using ID.  Bingo it works, 550 participants came yearly and I work with these 550 rows of data, 56 columns (1 ID, 10 activities x 5 yrs). And I proceed to analysis changes of activities in year 1 to year 5, e.g. yearly increment.  All looks good.

But now, when I try to plot chart of the activities, I run into problem.  I don't have a YEAR parameter for the x-axis.

My x-axis of Activity 1 is Act1@2012, Act1@2013, Act1@2014, Act1@2015, Act1@2016

My x-axis of Activity 2 is Act2@2012, Act2@2013, Act2@2014, Ac2t@2015, Act2@2016

:

:

But I want the x-axis of all my 10 charts to be 2012 to 2016.  Also, I want all the 10 charts (later in my dash board) to have year 1 in similar colour, year 2 in another similar colour, etc and with a legend. So does that mean I should not joint the data but suppose to do a union in the first place?

But if I union in data in the first place, how can I obtain the 550 participants who came for all 5 years?

Thank you so much.

• ###### 1. Re: Joint or Union data set

Hi Siang,

If the columns in all the data sets have similar data types, you can go for union. To identify the 550 participants which came for all 5 years, you can create a calculated field as SUM({FIXED [Participant ID] : COUNTD(Year)})  and use it as a filter where count is equal to 5.

Also if you want all your charts to have same x-axis, you can use the fixed axis.

Hope this helps.

Thanks,

Nachiket

1 of 1 people found this helpful