SOLVED
Filtering for patchy history
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
lcoombes
5 - Atom
‎07-06-2021
06:33 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi everyone,
I have a data table like the below
Year 1 | Year 2 | Year 3 | Year 4 | |
Service 1 | 59 | 92 | ||
Service 2 | 100 | 115 | 120 | |
Service 3 | 200 | 151 | ||
Service 4 | 98 | 76 | 65 | 99 |
Service 5 | 24 | 63 | ||
Service 6 | 100 | 99 | 84 |
I want to analyse the services with 2 years of data separately to those with more than 2 years available.
So the data would be split into two tables like these:
Year 1 | Year 2 | Year 3 | Year 4 | |
Service 1 | 59 | 92 | ||
Service 3 | 200 | 151 | ||
Service 5 | 24 | 63 |
Year 1 | Year 2 | Year 3 | Year 4 | |
Service 2 | 100 | 115 | 120 | |
Service 4 | 98 | 76 | 65 | 99 |
Service 6 | 100 | 99 | 84 |
Does anyone have any idea how I might do this?
Thank you very much in advance 🙂
Solved! Go to Solution.
Labels:
- Labels:
- Help
2 REPLIES 2
mmenth
11 - Bolide
‎07-06-2021
07:18 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @lcoombes,
The attached worked for me! Essentially you transpose the data, remove empty/nulls value fields, count the remaining, split the data using a filter based on your criteria, then join each set back to the original, removing the right join data fields.
Best,
mmenth
‎07-06-2021
07:21 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks so much for such a quick reply @mmenth, very clever!! Much appreciated 😀