Replace Text in field and calculate statistics with arcade

[DoZ]

Hello everybody!

Yeah, that´s me again... trying to learn Arcade but hitting my head against the wall!

I have a list of Covid variants reports for every day. Sometimes the “Delta” has different naming but remains a Delta (for example the “B.1.617.2” and the “B.1.617.2.2”).

Variant	Date
B.1.617.2 (Delta)	03.01.2022
B.1.1.529 (Omicron)	04.01.2022
B.1.617.2.2 (Delta)	04.01.2022
B.1.1.529 (Omicron)	04.01.2022
B.1.1.529 (Omicron)	05.01.2022
B.1.617.2 (Delta)	05.01.2022
B.1.617.2 (Delta)	07.01.2022
B.1.1.529 (Omicron)	08.01.2022
B.1.617.2.2 (Delta)	09.01.2022
B.1.617.2. (Delta)	09.01.2022

My goal is to have a list in the dashboard that looks similar to this:

Introducing Data Expressions in ArcGIS Dashboards (esri.com)

Where instead of the name of the County I will have the name of the variant and instead fo the “sites” I will have the percentage of that variant. It should look like the following graph, but as a list:

Only problem I have to sum up all Deltas first and then rename them simply 'Delta'.

I should Also rename ‘B.1.1.529 (Omicron)’ simply ‘Omicron’.

I thought first to replace the names but stuck immediately there….I think I am not writing the correct “replace” sentence.

I started like this:

    var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), 'xxxxx' , 0, ['*'], false);

    var fs_renamed = replace(['Variant'], 'B.1.617.2 (Delta)', 'Delta')

    var fs_renamed1 = replace(['Variant'], 'B.1.617.2.2 (Delta)', 'Delta')

    return fs_renamed1

I Also wanted to copy from this example, but I don´t know how to reference my field correctly. Or if this could be use in a list widget.

https://community.esri.com/t5/arcgis-online-ideas/allow-multiple-replace-functions-in-one-arcade/idi... 

Any suggestion will be much appreciated!!

Thank you very much!!

[jcarlson]

Hello again!

I think you might be able to do this with a GroupBy, actually. Do the variants always have "(Delta)" or "(Omicron)" in the text somewhere? If so, you could try using the SQL expression in GroupBy to search for those strings using LIKE, and rename them all inside of a CASE... WHEN... THEN... block.

Try something like this:

var fs = FeatureSetByPortalItem(
    Portal('https://www.arcgis.com'),
    'xxxxx',
    0,
    ['*'],
    false
);

var groupSQL = "CASE WHEN Variant LIKE '%Delta%' THEN 'Delta' WHEN Variant LIKE '%Omicron%' THEN 'Omicron' ELSE '' END";

var total = Count(fs);

var grouped = GroupBy(
    fs,
    {name: 'Variant', expression: groupSQL},
    [
        {name: 'Cases', expression: 'cases', statistic: 'SUM'},
        {name: 'Percent of Total', expression: 'cases / @total', statistic: 'SUM'}
    ]
)

return grouped

[DoZ]

Hello Josh,

thanks againg for your prompt response (u r a hero!)!

I tried out your script with a feature layer that has exactely the same data as in my example table, but althouth the test function gives no errors, I am not allowed to use the expression...there most be something wrong that I can´t figure out.

I think I red somewhere that Arcade dos not support wildcards? Is that true?

And I can´t find the "CASE WHEN THEN" function in the Arcade Doc....I thought I had to use a replace function . 

Text Functions | ArcGIS Arcade | ArcGIS Developer 

Thank you again soo much!!

Kindest regards

[jcarlson]

Hello again!

So, CASE statements aren't an Arcade thing, they're SQL. But within an Arcade function like GroupBy, we have the opportunity to provide the SQL command that is being executed. We don't have access to the full toolbox of SQL, but using CASE statements inside of a group/filter/order function is possible.

While it is true that Arcade doesn't do wildcards, SQL does, so it's fine to use them inside of that statement within the GroupBy function.

I'm not sure why it isn't working, though. Usually a no-error failure means that there was an invalid value in the output FeatureSet. Since we're doing some math, it may be performing an invalid calculation.

Try omitting the "percent of total" field from your output and see if it gives you anything different. Otherwise, we may need to approach this differently.

[natasha]

Hi Josh, I have a similar question to DoZ but in my case the "variants" don't contain the same characters. Do you know if/how one might instead of CASE WHEN use replace or decode?

Here's my question for more context:

Re: How to combine, replace (or decode), and group... - Esri Community

Thanks!

[DoZ]

@jcarlson Hey Josh, hi How are you?

I was wondering if you could have a bit of time to review your answer....unfortunally didn´t work out with the  "CASE WHEN THEN". Do you know where I can find some documentation regarding the "CASE WHEN THEN"? Or is it another way to do this?

Thank you so much!

Kindest regards!