Combining Data on Value and Date

[pablomtz28]

Hi all,

I have some data that has many information including personal id and a start year. The personal id can be repeated throughout the data, but per personal id, there is only a unique start year, such as this:

Data

Personal ID	Start Year
0001	2010
0001	2011
0002	2012
0002	2013

I am trying to combine the data so the personal id is not repeated, and I get a range of years per personal id, so end up with something like this:

Output

Personal ID	Years
0001	2010-2011
0002	2012-2013

Anyone know how to do this? Is it possible?? Thanks in advance!

[Lyser]

could you use the summarize tool to group by Personal ID, and calculate the MIN and MAX start years?

you could then use the formula tool to create the a field containing the MIN and MAX as a range.

[christine_assaad · Accepted solution]

Hi @pablomtz28 

Please see below a way to do it.

[pablomtz28]

Thank you very much, that was very helpful!

I have another follow up question, I dont know if you might know the answer to this, as I found a better way to approach this problem I had.

I am dealing with the same data, but instead of dealing with ranges as mentioned before, I want to add five new columns that relate to the years 2016-2020 and simply add a boolean per Personal ID to check wether they were a part of the event that year.

Input:

Personal ID	Year
0001	2016
0001	2017
0001	2020
0002	2018
0002	2019
0002	2020

Output:

Personal ID	2016	2017	2018	2019	2020
0001	Yes	Yes	No	No	Yes
0002	No	No	Yes	Yes	Yes

Is it possible maybe?? Thanks in advance!

[christine_assaad · Accepted solution]

Hi @pablomtz28 

Is this what you are looking for?

[pablomtz28]

Hi @christine_assaad

I have one quick follow up regarding the previous questions regarding adding columns per Personal ID with the years they have participated. In every row, each Personal ID has different information such as Birth Year, City, State, etc. that I want to preserve once I have my final output. Every row that has the same Personal ID has the exact same information, except, of course, the Year Start that we used to have our final output. Is there a way to preserve that data for the output?

For example,

Personal ID	Year Start	City	State
0001	2016	Austin	Texas
0001	2017	Austin	Texas
0002	2018	New York	New York
0002	2019	New York	New York

Output:

Personal ID	2016	2017	2018	2019	City	State
0001	Yes	Yes	No	No	Austin	Texas
0002	No	No	Yes	Yes	New York	New York

Please let me know if you need any additional information.

Thank you very much once again!

[christine_assaad]

Hi @pablomtz28 

Yes. You will just need to add City and State to your "Group By" fields in the "CrossTab".

Please see attached. Cheers!