topic Re: Count Multiple Items in a Field in Connectivity & Data Prep

Count Multiple Items in a Field

AMorella — Mon, 25 Sep 2023 15:25:18 GMT

I'm working on an app to calculate the total cost per unit. The only challenge is there may be multiple units indicated in a single field and I need to divide by the total count of units of all rows.

For example:

BEAU5526356, GCXU5523584, TCNU4967930 are in 1 row of data, so I'd need to show a count of 3 for this row.

Is anyone aware of a means to count these rows and then calculate a total? The total part should be straightforward; but I've no idea how to calculate a count within a field.

Thanks in advance!

Re: Count Multiple Items in a Field

vincent_ardiet_ — Tue, 26 Sep 2023 13:42:44 GMT

You can use SubStringCount and count the commas + 1.

Re: Count Multiple Items in a Field

AMorella — Tue, 26 Sep 2023 14:11:02 GMT

Vincent,

Thank you. This works perfectly!

Re: Count Multiple Items in a Field

vincent_ardiet_ — Tue, 26 Sep 2023 14:20:23 GMT

Note that you can also use SubField in your loading script to split those strings into rows (in a separate table for example). But it depends of what you want to do next and of the amount of data you have. However SubStringCount could be enough for your need.