How to get the count of individual items in a list

Question & Answer
,
2

Hi @HarveyB-B ,

Let me see if I understand. Given a few ‘flaglist’ strings like these:

ABCD
EA
FHI
FA
DF

You’re looking for the letter count, like this:

A: 3
F: 3
D: 2
B: 1
C: 1
E: 1
H: 1
I: 1

And it’s not just the letter count, there may be combinations of letters, like:
X1, X2, …, X10.

If that’s a description of the problem that we’re solving, a few clarifying questions, if I may:

  1. What is the frequency of the data? How often does it change and how is it updated?
  2. What is the ‘grain’ of the reporting? Other than the bar chart that shows the counts, is there a date/time dimension to the report? Are there other dimensions?
  3. What filters are part of the display

There are techniques that you can use in Quicksight to count ‘known’ characters / character combinations. The posting here shows how you can count occurrences of a string from a parameter. That’s similar to the SQL command described in a youtube, here.

But, to solve this generically, for any arbitrary character combination, I think that you have to iterate through each combination. That likely points to solving this in the data source; e.g. you’d use a SQL technique and then have a table with every combination of characters and their counts for each dimension. At that point, Quicksight would be able visualize the count across all the dimensions.

ws