Forum Discussion
tgjones43
Helper IV
Helper IVCustom column based on several other columns
This is a tricky one to describe, so here goes. I have attached a much simplified example. The data relates to fish sampling data in rivers. I have a table with thousands of 8-digit numbers in th...
v-lili6-msft
Community Support
Community Supporthi, Ola_S
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
tgjones43Helper IV
Helper IVHi v-lili6-msft and Ola_S. Thank you so much for your help. I have only just got round to trying the code. It works very well, but it doesn't quite match the expected outcome...
I have pasted a slightly longer view of my data below, this time with two values for [ID]. The main problem is that where [ID2] is null, the value in [Expected Outcome] should also be null, but as you can see in [Custom Column], it is displaying Survey 1. And it doing this for every null value in [ID2], no matter what the value of [ID].
Can you suggest a tweek to your solution?
Thank you.
| ID | ID 2 | Name | Value | Custom Column | Expected Outcome |
| 21771061 | null | Altitude - | 84 | Survey 1 | null |
| 21771061 | null | Area - | South East | Survey 1 | null |
| 21771061 | null | CEH 50K catchment id - | 196541 - Severn Upper above Montfort C054001I | Survey 1 | null |
| 21771061 | null | CEH 50k catchment id number | 196541 | Survey 1 | null |
| 21771061 | 21778928 | Site purpose - First survey | 01/01/2015 | Survey 3 | Survey 2 |
| 21771061 | 21778928 | Site purpose - Guideline method | Electric Fishing | Survey 3 | Survey 2 |
| 21771061 | 21776925 | Site purpose - Guideline method | Electric Fishing | null | Survey 1 |
| 21771061 | 21777935 | Site purpose - Guideline method | Electric Fishing | Survey 2 | Survey 3 |
| 21771061 | 21777935 | Site purpose - Program | Core Fisheries Monitoring Programme | Survey 2 | Survey 3 |
| 21771061 | 21776925 | Site purpose - Program | Core Fisheries Monitoring Programme | null | Survey 1 |
| 21771061 | 21778928 | Site purpose - Program | Core Fisheries Monitoring Programme | Survey 3 | Survey 2 |
| 21771061 | 21777935 | Site purpose - Purpose | SAP / ST - Spatial | Survey 2 | Survey 3 |
| 21771061 | 21778928 | Site purpose - Purpose | SAP / ST - Spatial | Survey 3 | Survey 2 |
| 21771061 | 21776925 | Site purpose - Purpose | Coarse - Temporal | null | Survey 1 |
| 21771061 | 21777935 | Site purpose - Repeat frequency | 6 Years | Survey 2 | Survey 3 |
| 21771061 | 21776925 | Site purpose - Repeat frequency | 1 Year | null | Survey 1 |
| 21771061 | 21778928 | Site purpose - Repeat frequency | 6 Years | Survey 3 | Survey 2 |
| 21771061 | 21776925 | Site purpose - Repeat from | 01/01/2001 | null | Survey 1 |
| 21771061 | 21778928 | Site purpose - Repeat from | 01/01/2012 | Survey 3 | Survey 2 |
| 21771061 | 21777935 | Site purpose - Repeat from | 01/01/2001 | Survey 2 | Survey 3 |
| 21771061 | 21776925 | Site purpose - Repeat until | 31/12/2006 | null | Survey 1 |
| 21771061 | 21778928 | Site purpose - Repeat until | 31/12/2025 | Survey 3 | Survey 2 |
| 21771061 | 21777935 | Site purpose - Repeat until | 31/12/2006 | Survey 2 | Survey 3 |
| 21771061 | 21776925 | Site purpose - Survey after | 01/01/2001 | null | Survey 1 |
| 21771061 | 21778928 | Site purpose - Survey after | 01/01/2012 | Survey 3 | Survey 2 |
| 21771061 | 21777935 | Site purpose - Survey after | 01/01/2001 | Survey 2 | Survey 3 |
| 21771061 | 21777935 | Site purpose - Survey before | 31/12/2001 | Survey 2 | Survey 3 |
| 21771061 | 21776925 | Site purpose - Survey before | 31/12/2001 | null | Survey 1 |
| 21771061 | 21778928 | Site purpose - Survey before | 31/12/2012 | Survey 3 | Survey 2 |
| 21771061 | 21777935 | Site purpose - Survey strategy | Qualitative | Survey 2 | Survey 3 |
| 21771061 | 21778928 | Site purpose - Survey strategy | CPUE/T | Survey 3 | Survey 2 |
| 21771061 | 21776925 | Site purpose - Survey strategy | Quantitative | null | Survey 1 |
| 21771061 | 21776925 | Site purpose - Target guild | Coarse | null | Survey 1 |
| 21771061 | 21778928 | Site purpose - Target guild | Salmon / Sea Trout | Survey 3 | Survey 2 |
| 21771061 | 21777935 | Site purpose - Target guild | Salmon / Sea Trout | Survey 2 | Survey 3 |
| 21771062 | null | Altitude - | 82 | Survey 1 | null |
| 21771062 | null | Area - | South East | Survey 1 | null |
| 21771062 | null | CEH 50K catchment id - | 196541 - Severn Upper above Montfort C054001I | Survey 1 | null |
| 21771062 | null | CEH 50k catchment id number | 196541 | Survey 1 | null |
| 21771062 | 21777936 | Site purpose - First survey | 01/01/2015 | Survey 5 | Survey 5 |
| 21771062 | 21777936 | Site purpose - Guideline method | Electric Fishing | Survey 5 | Survey 5 |
| 21771062 | 21776926 | Site purpose - Guideline method | Electric Fishing | Survey 4 | Survey 4 |
| 21771062 | 21777936 | Site purpose - Program | Core Fisheries Monitoring Programme | Survey 5 | Survey 5 |
| 21771062 | 21776926 | Site purpose - Program | Core Fisheries Monitoring Programme | Survey 4 | Survey 4 |
| 21771062 | 21776926 | Site purpose - Purpose | Coarse - Temporal | Survey 4 | Survey 4 |
| 21771062 | 21777936 | Site purpose - Purpose | SAP / ST - Spatial | Survey 5 | Survey 5 |
| 21771062 | 21776926 | Site purpose - Repeat frequency | 1 Year | Survey 4 | Survey 4 |
| 21771062 | 21777936 | Site purpose - Repeat frequency | 6 Years | Survey 5 | Survey 5 |
| 21771062 | 21777936 | Site purpose - Repeat from | 01/01/2001 | Survey 5 | Survey 5 |
| 21771062 | 21776926 | Site purpose - Repeat from | 01/01/2001 | Survey 4 | Survey 4 |
| 21771062 | 21777936 | Site purpose - Repeat until | 31/12/2025 | Survey 5 | Survey 5 |
| 21771062 | 21776926 | Site purpose - Repeat until | 31/12/2006 | Survey 4 | Survey 4 |
| 21771062 | 21776926 | Site purpose - Survey after | 01/01/2001 | Survey 4 | Survey 4 |
| 21771062 | 21777936 | Site purpose - Survey after | 01/08/2013 | Survey 5 | Survey 5 |
| 21771062 | 21777936 | Site purpose - Survey before | 31/10/2013 | Survey 5 | Survey 5 |
| 21771062 | 21776926 | Site purpose - Survey before | 31/12/2001 | Survey 4 | Survey 4 |
| 21771062 | 21776926 | Site purpose - Survey strategy | Quantitative | Survey 4 | Survey 4 |
| 21771062 | 21777936 | Site purpose - Survey strategy | CPUE/T | Survey 5 | Survey 5 |
| 21771062 | 21777936 | Site purpose - Target guild | Salmon / Sea Trout | Survey 5 | Survey 5 |
| 21771062 | 21776926 | Site purpose - Target guild | Coarse | Survey 4 | Survey 4 |