Need Assistance Filtering and Recreating Data Fields Based on a List

Question & Answer
1

I have a field which lists maybe 1000 different unique shopping lists. Some are similar, some are extremely different. Each one will have a count.
Screenshot of concept visualization:

image

In a table, it would look like this:

List Count
[“Pizza Dough”, “Tomato Sauce”, “Cheese”, “Bananas”, “Apples”, “Toothpaste”] 23
[“Bread”, “Oranges”, “Apples”, “Bananas”, “Watermelon”, “Pasta”, “Mozzarella”] 16
[“Kiwi”, “Watermelons”, “Peaches”, “Cookies”, “Toy Car”, “Butter”] 3

I have a list of items i want to extract. In this example, lets say its fruits. So my list is pre-defined, therefore for this example would be bananas, apples, oranges, watermelon, peaches, kiwi. In my real-time one it would have around 40 names of fruits to be extracted. How can i extract each individual one, and have a count for it?

So apples would have a count of 39 (23 + 16), oranges have a count of 16, watermelons have a count of 19 etc. Is this possible to do in QuickSight?

I’m unable to send screenshots, as its a made up scenario which i can apply to my real-time goal, but i can draw or recreated whatever is needed. Any help is appreciated

-Thanks

2

The typed in table was transformed a bit when i submitted it. its “List”, “Count” (how many times that list appears)

3

i guess you will have a lot items as well, right? that it will not be the best way to create “is_apple”, “is_orange” etc.

4

I have created a flag for each one. I have 25 total flags. It then lists the count of each one. It was a long process but works.

Another thing i needed to do was create a new list, but only return the fruits in each list. Would this be possible? So the lists i listed would now return as:

[“Bananas”, “Apples”]
[“Oranges”, “Apples”, “Bananas”, “Watermelon”]
[“Kiwi”, “Watermelons”, “Peaches”]

In general, how can i create a calculated field which looks in my list of 25 fruits, and return the hundreds of lists which only now have the fruits in?

5

I’ve started to create a long nested ifelse statement. the only problem is that once the flag is found, it stops the chain. for example

ifelse({flagApples} = ‘1’, 'Apples, ',
ifelse({flagBananas} = ‘1’, 'Banana, ',
ifelse({flagOranges} = ‘1’, ‘Oranges ‘,’’)))

This looks at each one and will find if they have apples, bananas, oranges. But say a list is flagged as having apples (flagApples = 1), it will end the ifelse chain and won’t look to see if it has bananas and oranges.

How can i change this so it will do the if statement, and regardless if it is flagged or not, it will continue to the next if statement?

Or should i do a Concat where flags = 1? As this would add the strings into a list?

Yet again, still new to quicksight. Any replies would be appreciated, and if you’re confused on what i’m on about just ask and i can simpler it down.

-Thanks

6

Ended up finding a solution. What i did was concatenated every flag:

concat(
ifelse({flagApples} = ‘1’, 'Apples, '),
ifelse({flagBananas} = ‘1’, 'Banana, '),
ifelse({flagOranges} = ‘1’, ‘Oranges, ')
)

This then looks at every list, return the fruit if found. the one problem i came across was that QuickSight restricts it to 20 strings/fields per catenation. There i created 2 seperate temp fields, which stored 20 in one, 5 in the other (as i had 25 flags). I then concatenated these two temp fields together, and used that in my final table

7

As you may see i have solved this, however i now have another thing i want to do.

SCENARIO
I have a list of 25 fruits AND vegetables. Each fruit now has a unique flag. There are 13 fruits, each one is A-M (if true) - For example Apples = A if true, Banana = B if true and so on. Each vegetable also now has a flag, being X1-X12 if true (this is the same concept as fruits was).

Instead of having these 25 separate flags, how can i group them into one field.

So when i want to see this visually, i don’t have to drag 25 fields into it (one for each flag), instead i can drop the one which contains all flags, which shows all 25 fruits and vegetables by the count. Is this possible to achieve?

8

Could you bring the logic of the flags into one field?

9

Haven’t tried at the moment, will do in a bit. Instead of having 25 ifelse statements in one calculated field, is there a way to have each flag field name in a list which will then display like the following:

image

10

Not sure also if possible as when i was using concat, i could only have up to 20 fields / strings added. I think it is the same for ifelse statements, but i could be wrong

11

The only other thing i can think of to approach this is this:

I have a list which looks like this:

Apple (A), Banana(B), Peach(P), Broccoli (X1), Carrot (X3). (This list has a count of 3)
Which returns
ABPX1X3
Which looks like this visually
image

I have the same thing for many variations of lists, such as
DK count 5,
FKX4 count 8,
and so on.

Is there a way i can extract the count for each character / x(number)

I was thinking creating a calculated field which looks in newShoppingList (which only has the fruit and vegetable coded A-N, X1-X10), and creates a count for each time A-N X1-X10 is found. This should create a count for each individual fruit / vegetable based of the character associated to it?

I’m not sure how to apply this though, if anyone has any ideas on calculated fields please respond.

-Thanks

12

Hello @HarveyB-B and @ErikG !

@HarveyB-B were you able to find a solution for this, and if so could you share it to help the community?

As for your last question, you may be able to achieve this with the functions below:

or

13

Hello @HarveyB-B and @ErikG !

It has been some time since there has been activity on this question, but we would still like to help find a solution. Are you still running into this problem or were you able to resolve this, and if so could you share the solution to help the community?

If we do not hear from you in 3 days this topic will be archived.

14

Feel free to archive, as no working solution found and no longer needing help on this subject

-Thanks

1 Like
16

@HarveyB-B No problem!

If you continue to run into this problem I would recommend making an AWS support ticket to help diagnose the problem and have a deeper dive with your set up.

Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.