Search code examples
regexif-statementsplitamazon-quicksight

Amazon QuickSight extract a string from a comma separated unsorted string using calculated field

I am working on a QuickSight table graph, where the Dataset has a field that is a set of words separated by ',' (commas). These words are not sorted in any order, but for each row of the dataset, there might be 1 or more words and they can be repeated across the rows, but not for the same data row.

giraffe, lion, apple, mango, dog, frog, banana
lion, apple
banana, apple, giraffe, lion, mango, dog
giraffe, mango, dog, banana, lion, apple
mango, dog, frog, giraffe, banana, apple
frog
giraffe
lion, giraffe, dog

I want to separate these into individual 'calculated fields', where I want just that word if it is in the row to be shown and a NULL, if the word is not in that complete row. I am using the following construct for it:

calculated field name: 
"giraffe"

value to calculate the field:

ifelse(
    (
        (split({AnimalFruit}, ',', 1) = "giraffe") OR 
        (split({AnimalFruit}, ',', 2) = "giraffe") OR  
        (split({AnimalFruit}, ',', 3) = "giraffe") OR  
        (split({AnimalFruit}, ',', 4) = "giraffe") OR  
        (split({AnimalFruit}, ',', 5) = "giraffe") OR  
        (split({AnimalFruit}, ',', 6) = "giraffe") OR  
        (split({AnimalFruit}, ',', 7) = "giraffe") OR  
        (split({AnimalFruit}, ',', 8) = "giraffe") OR  
        (split({AnimalFruit}, ',', 9) = "giraffe") OR  
        (split({AnimalFruit}, ',', 10) = "giraffe")
    ),
    "giraffe",
    NULL
)

But the above just gives me the rows where the 'giraffe' is the first word, and not the lines where it might be the 2nd or 3rd or later in the row.

I am not sure what I am doing wrong here, as the logic seems to be correct to me. Is there something else I can do to get this result?

The output I am expecting is:

giraffe

But I get:

enter image description here

I have also tried using the following construct as given in the AWS page for 'ifelse':

https://docs.aws.amazon.com/quicksight/latest/user/ifelse-function.html

ifelse(
    (split({Flags}, ',', 1)) = "giraffe", "giraffe",
    (split({Flags}, ',', 2)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 3)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 4)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 5)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 6)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 7)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 8)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 9)) = "giraffe", "giraffe", 
    (split({Flags}, ',', 10)) = "giraffe", "giraffe", 
    NULL
)
Solution

  • Have resolved the issue. Was a problem with how the CSV dataset is created, where it allows for a " " (space) between the "," (comma) and the next word.

    When I was searching for the word, the first time, it did not have that space, but for each subsequent one, there was a space and that had to be catered for in the query construct:

    ifelse(
    (
        (split({AnimalFruit}, ',', 1) = "giraffe") OR 
        (split({AnimalFruit}, ',', 2) = " giraffe") OR  
        (split({AnimalFruit}, ',', 3) = " giraffe") OR  
        (split({AnimalFruit}, ',', 4) = " giraffe") OR  
        (split({AnimalFruit}, ',', 5) = " giraffe") OR  
        (split({AnimalFruit}, ',', 6) = " giraffe") OR  
        (split({AnimalFruit}, ',', 7) = " giraffe") OR  
        (split({AnimalFruit}, ',', 8) = " giraffe") OR  
        (split({AnimalFruit}, ',', 9) = " giraffe") OR  
        (split({AnimalFruit}, ',', 10) = " giraffe")
    ),
    "giraffe",
    NULL
)

    Doing so, resolved the issue and I get the desired output.