Question

Fill in missing years in chart

  • 23 September 2022
  • 8 replies
  • 1 view
deanhowell
Badge +9

I have some data that I have run through the statistics transformer in the format shown below i.e. ID, Year, Count

 

imageI want to create a chart but need to include the years that have a zero value in a five year period i.e. 2015 to 2019.

 

How do I manipulate the data to add in the missing years with a zero value?

 

Any assistance would be greatly appreciated.

 

8 replies

carmijo
Rank
Badge +10

Hey there.

 

If I understand correctly, you want to create a full list with all years even if they’re not included in your data. If the year isn’t included in your data, you want the year count to be 0.

 

You can do this by

  1. creating a list of possible years and set each total to 0 (I would use excel)
  2. then join the two lists by year using a featurejoiner. When conflicts with the job count occurs, use the count attribute from your data.
  3. You should now have a list with all years and a count of zero for the years that weren’t in your original list

 

 

--

If i've provided something helpful, please upvote the comment. If i've provided a solution, please mark it as 'Best Answer'. This marks the post as answered and helps the community find the solution. If I didn't provide a solution, please let me know if I can help further. Thanks! 🙂

deanhowell
Badge +9

Hey there.

 

If I understand correctly, you want to create a full list with all years even if they’re not included in your data. If the year isn’t included in your data, you want the year count to be 0.

 

You can do this by

  1. creating a list of possible years and set each total to 0 (I would use excel)
  2. then join the two lists by year using a featurejoiner. When conflicts with the job count occurs, use the count attribute from your data.
  3. You should now have a list with all years and a count of zero for the years that weren’t in your original list

 

 

--

If i've provided something helpful, please upvote the comment. If i've provided a solution, please mark it as 'Best Answer'. This marks the post as answered and helps the community find the solution. If I didn't provide a solution, please let me know if I can help further. Thanks! 🙂

Thanks @carmijo​, yes I want to create a full list, where the years not in the data are assigned a value of zero.

 

I think it might be something to do with the ID field or the Year field type not matching.

 

Unfortunately your approach did not give me the results that I need but I will keep trying.

geomancer
Rank
Userlevel 4
Badge +36

Take a look at the solution to this question. I think you can adapt the solution to your needs.

deanhowell
Badge +9

Take a look at the solution to this question. I think you can adapt the solution to your needs.

Thanks that is what i was trying to work with but just can't get the right answer. I think it is because I need it to be based on two attributes, the id and year. I need 5 records for each id so might just need a range of feature merges and joins.​

geomancer
Rank
Userlevel 4
Badge +36

There is always a way 😉

Sites_and_Years

ebygomm
Rank
Userlevel 1
Badge +10

Thanks that is what i was trying to work with but just can't get the right answer. I think it is because I need it to be based on two attributes, the id and year. I need 5 records for each id so might just need a range of feature merges and joins.​

No need for several feature mergers. Create the 5 years for each site by taking each individual site, cloning five times and then creating the year from the copynumber and starting year. Then featuremerge on Site Number and Year and keep only unmerged years (i.e. those not previously included in the data)

image

deanhowell
Badge +9

There is always a way 😉

Sites_and_Years

Thanks ​@geomancer​ very much appreciated and as you said there is always a way.

deanhowell
Badge +9

Thanks that is what i was trying to work with but just can't get the right answer. I think it is because I need it to be based on two attributes, the id and year. I need 5 records for each id so might just need a range of feature merges and joins.​

Thanks @ebygomm​ your assistance is always appreciated ​

Reply


Community Stats

28,670
Posts
109,365
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.