We have a .csv like this:
product_name, price, city, market_id
milk, 2.5, London, L14
milk, 2.4, London, L22
milk, 1.8, Berlin, B12
rice, 6.1, Lisbon, LB3
We want to add all the values of the column price for the rows which fulfill a certain condition, e.g. product_name == "milk" and city == "London". In the previous example, that should be 4.9.
Basically we need to add the data for a column after the user has selected the product_name and the ' city' . What is the easiest way to achieve this? These are the options we've looked into:
pulldata() seems to only be useful to retrieve a single value from the .csv, but we need a collection of them and adding them.
Select from file and search() are used to populate a choice list, but we have no interest in displaying the values individually to the user for them to select, we only want to automatically populate a field showing the total sum.
A JS script seems like the next best option, but they are not able to access local files.
Thanks in advance.
I don't think there is a way to do exactly what you are trying to accomplish in Survey123. I'm not sure how you're populating the csv but the easiest solution is probably doing the addition beforehand - is there a reason why you couldn't just have another csv list with columns for product name, city, total_price?
product_name, total_price, city
milk, 4.9, London
milk, 1.8, Berlin
rice, 6.1, Lisbon
This should be relatively easy to complete if you combine choice filters with Pulldata. Choice filter for city, then a conditional list with product_name, and finally pulldata to grab your price.
Edit: Sorry, it's early. Didn't see the other requirement. I still think this could be possible, but I believe the issue is your test data in this case - the setup is quite confusing. Any chance we can see the real data set? Could really help.