Retrieving multiple values as string from csv with instance

Support
1

Dear community,

Is it possible to get all values in a column of a csv or entity that correspond to the filter as a string?

As an example this standard string only returns the first value corresponding to the filter:

instance('external_csv')/root/item[search_column = ${matching_value}]/value

But the external_csv contains multiple rows that correspond to the filter:

The goal would be to return all these values in a string:

vale_1, value_2, value_3

The values returned would be the same as in a select_multiple_from_file question with a choice_filter applied but in our case the string would be used per example in a note or hint to guide remunerators.

Thanks for any suggestions.

2

You should be able to use the join function for this:

join(" ", instance('external_csv')/root/item[search_column = ${matching_value}]/value)

If you want it to be exactly like the result of a select multiple, join with a " " as I showed. You can also join with ", " or any other separator you would like.

If without a join or some other way of accessing a specific or multiple matches, the expression is interpreted as

instance('external_csv')/root/item[search_column = ${matching_value}][position() = 1]/value)

Notice the extra [position() = 1] -- the first match is automatically used. And yes, you can use a second expression in square brackets to index into a filtered result set!

3

Thank you @LN ! This works!

In a further step I want to retrieve just a part of the values as a number and save it in a comma separted string to retrieve the highest value in the string.

As an example:

The values to retrieve with

join(", ", instance('external_csv')/root/item[search_column = ${matching_value}]/value)

look like this: CRS-2344-DAF1, CRS-2344-DAF2, CRS-2344-DAF3

I would like to retrieve only the last number of the values in the string: 1, 2, 3

and then get the highest value in that string with max().

I tried it with this:

max(join(", ", substr(instance('external_csv')/root/item[search_column = ${matching_value}]/value, 12)))

But this returns just the first value (1 in the example) and not the highest (3 in the example).

A way out is to hardcode this with:

instance('external_csv')/root/item[search_column = ${matching_value}][position() = 1]/value)

and save each value in a seperate field and then do max() calculations on these fields.

This workaround works with a limited number of values. But not if there are hundreds of values such as:

CRS-2344, CRS-2345, CRS-2345, CRS-2347, CRS-2348, ...

with the aim to retrieve first the highest number (2348) and then generate a new code for p.e. agricultural plots based on the existing codes already stored in an entity-list (or a csv). In the example the new code automatically generated should be: CRS-2349

Is there any solution to this?

Thanks for any hints.