Fill new field with a subset of values from another field, based on conditions in two other fields

[MichelleShaffer]

Hello!

I am sure that I am over complicating how to do this in my head but...

I have a .dbf table in ArcPro where I would like to create a new field that contains values from a different field, but values subset based on conditions in two other fields (see the attached photo). 

Specifically, I would like to fill the field "FWOA_Value" with values from the field "Value" when the field "Name" is X and the field "Percentage" is Y. 

I have tried this with an if else statement but I am not sure now to set this condition in "return".

Any pointers would be greatly appreciated!

[JohannesLindner]

Field Calculator, Arcade:

if($feature.Name == "Atlantic Croaker" && $feature.Percentage == 5050) {
    return $feature.Value
}
return null

[MichelleShaffer]

Hello Johannes,

Yes, here the if() statement is correct, however the return is not quite what I need.  

For return, I need to return a subset of $feature.Value. In the case you gave above, I would need to return  $feature.Value but for $feature.Name == "Atlantic Croaker" && $feature.Percentage == 100100. I am not sure how to implement this in the return. Any ideas?

thank you!

[JohannesLindner]

Wouldn't that be as simple as changing the condition? Or am I misunderstanding you?

if($feature.Name == "Atlantic Croaker" && $feature.Percentage == 100100) {
    return $feature.Value
}
return null

Or, to stay with the X/Y notation from your question:

var X = "Atlantic Croaker"
var Y = 100100

if($feature.Name == X && $feature.Percentage == Y) {
    return $feature.Value
}
return null

[MichelleShaffer]

My apologies, I may not be explaining this clearly!

so for rows that are 

$feature.Name == "Atlantic Croaker" && $feature.Percentage == 5050

 I need to fill a new column $feature.FWOA_Value with $feature.Value from 

$feature.Name == "Atlantic Croaker" && $feature.Percentage == 100100

This would be my 'return' in an if else. Basically, I need to fill one subset of rows with values from another subset of rows. Does that make more sense? 

[JohannesLindner]

Ah, OK.

So, if you only care about Atlantic Croaker and 5050, this should work:

if($feature.Name == "Atlabtic Croaker" && $feature.Percentage == 5050) {
    // Filter the table
    var target = First(Filter($featureset, "Name = 'Atlantic Croaker' AND Percentage = 100100"))
    // nothing found? -> return null
    if(target == null) { return null }
    return target.Value
}
return FWOA_Value

If you care about multiple combinations of Name and Percentage, you can either calculate each combination manually (change the if statement and change the Filter query) or you can do it automatically.

To do it automatically, please answer these questions about your data:

• do you have more Percentage values (other than 5050 and 100100)?
• what value do you want to copy for "AtlanticCroaker" & 100100?
• what value do you want to copy for "AtlanticCroaker" & 2525?
• what value do you want to copy for "SomeSpecies" & 5050?

[MichelleShaffer]

Hello Johannas,

Thank you for the help! 

I tried to enter the code you provided and run it manually for column "FWOA_Value" > field calculator > arcade, however I received the "002717: invalid arcade expression for line 8". This is the "return FWOA_Value" part of the code. Could this error be because we are trying to fill the column "FWOA_Value" with the field calculator, so it can't be our return statement as well?

To answer your previous questions:

Do you have more percentage values?

 The percentage values are 100100, 5050, 6060, 8080, 5080, 6080, 8050, 8060, 9090, 10060, 10080, 60100, 80100. The "Name" or species are Atlantic Croaker, Brown Shrimp, White Shrimp, Gulf Menhaden, and Red Snapper. 

What value do you want to copy for "Atlantic Croaker" & 100100?

What value do you want to copy for "Atlantic Croaker" & 2525?

What value do you want to copy for "SomeSpecies" & 5050? 

I am not quite sure what you mean here, but for each if statement (every combination of species and percentage), I will need to fill the column "FWOA_Value" with the numbers in "Value" that are associated with that same species but percentage 100100. 

thank you for your time!

[JohannesLindner]

I was asking the questions to determine if you need the if statement at all. Turns out you don't, because you're mapping all the percentages to 100100.

// Filter the table for same species and percentage 100100
var species = $feature.Name
var target = First(Filter($featureset, "Name = @species AND Percentage = 100100"))
// nothing found? -> return null
if(target == null) { return null }
// else return that Value
return target.Value