Help with finding matches inside a complicated string

[Astraithious]

Hello everybody! I am new to this program but have been having an amazing time with it. I have finally hit a road block where my lack of programming knowledge has halted me but I figured since this community is so active I could find some help here.

The goal:   I want to be able to count how many times a string appears in a column, there may be multiple instances of it inside the same cell. I have 11 different ID that i would love to tally up from about 6400 entries.

Example Cell: [{"id":"52","type":"dp","character":"37852"}]

Example of multiple in a cell: [{"id":5,"type":"dp","character":"20560"},{"id":12,"type":"dp","character":"20560"}]

The useful information that I am seeking is the ID:XY portion but because they are in their own quotations they are throwing me off. I thought of trim but it wouldn't work as some cells are up to 2044 characters long. 

I also tried the filter and using the reg count function but I do not know enough of the programming side to allow me to sort through to get those matches.

End goal is to be able to tell how many IDs were from what time period but I can work on the time myself I'm sure.

I have included some sample data 

Thank you for any help that you can provide!

[Federica_FF · Accepted solution]

HI @Astraithious

the text inside the cart field is json.

Open the csv as normal csv (increase the length in the input tool) and use a JSON Parse Tool (Developer tab) for the cart colum. You'll get all the values splitted to rows.

For example:

[{"id":5,"type":"dp","character":"20560"},{"id":12,"type":"dp","character":"20560"}]

0.id    5
0.type    dp
0.character    20560
1.id    12
1.type    dp
1.character    20560

The first column has a starting number, which is the rowID.

The string right after the number is the column name

That number is what you need to count.

In attachment a short workflow to parse json strings and convert data into a regular columnar table

[MarqueeCrew]

Yes @Astraithious, I would use a regular expression.

regex_countmatches([cart],"id")

Regex_CountMatches([Field Name],"Expression") will count each of the occurrences of a data pattern.  If in your example, the letter combination id is unique, you could use the function above to count how many times you see it (id) in the field.  Records 19 & 20 are 50 occurrences.

Now if you don't feel comfortable with id (because it could match words like middle), then you might want to simply count the '}' character.

regex_countmatches([cart],"}")

Cheers,

Mark

P.S.  Your input tool will need to have option #7 (length) set to a number large enough to handle the string size.  254 is not long enough.

[Astraithious]

Absolutely fantastic! I had no idea the solution was so simple and elegant. Thank you very much!