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

    Joint or Union data set

    Siang Li Chua

      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
          Nachiket Dekhane

          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.

           

          Capture.PNG

           

          Hope this helps.

           

           

          Thanks,

          Nachiket

          1 of 1 people found this helpful