How to add the values of a column in CSV

90
2
10-07-2022 11:13 AM
A0_Data
New Contributor

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.

0 Kudos
2 Replies
AndyMcClary
New Contributor III

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

 

0 Kudos
abureaux
Occasional Contributor III

Hello,

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.

0 Kudos