Filter visuals with string that has got escape character

Question & Answer
, ,
1

Hello QuickSight Community :waving_hand:,

Long time reader, first time poster :sweat_smile:

The dataset used to generate one of our dashboards includes a field for User Agents. We also use this field to filter the dashboard visuals. In the dashboard, we display the User Agents in a table where a user can click on the User Agent to apply the filter (using a Navigation action to update a parameter which in turn updates the User Agent filter).

0236130c-e4fc-43b2-8092-518eabca085e
0236130c-e4fc-43b2-8092-518eabca085e1058×560 61.5 KB

However, when we click on one of the User Agents that has got an escape character (Screenshot above), we get the following error message displayed on all visuals.

Your calculated field expression contains invalid syntax. Correct the syntax and try again

Example of how the User Agent is formatted:

Mozilla/5.0 (Windows NT 10.1; x64; en-US) AppleWebKit/603.42 (KHTML, like Gecko) Chrome/52.0.1816.333 Safari/601.5ˤˢ%2527%2522\'\"
(Take note of the last 4 characters)

We tried following a solution suggested in the community for someone who had a similar issue https://community.amazonquicksight.com/t/how-to-escape-backslashes/632 where you escape the escape character itself which partially worked. We managed to escape the first escape character by adding two escape characters \

(Mozilla/5.0 (Windows NT 10.1; x64; en-US) AppleWebKit/603.42 (KHTML, like Gecko) Chrome/52.0.1816.333 Safari/601.5ˤˢ%2527%2522\\\)

but when we tried to add the single quote after, we got the same error message as above. Escaping the single quote did not help

Is there a way to parse the whole User Agent string without generating the error message?

1 Like
2

Hello @NetTimAnon, welcome to the QuickSight community! I think it would be better to remove the escape character altogether instead of replacing it. Do these characters always appear at the end of the string? If that is the case, you could use the substring function with the locate function to make the string end at the first appearance of the character. That would look something like this:

User Agent Modified =

ifelse(contains({User Agent}, "\"), 
substring({User Agent}, 1, locate({User Agent}, "\")-1),
{User Agent})

Alternatively, if it does not always appear at the end, then we will need to check for it at least twice to remove all instances of the character or we can check for each way it appears. That would look something like this:

User Agent Modified =

ifelse(contains({User Agent}, "\'\"), replace({User Agent}, "\'\", ""),
contains({User Agent}, "\\"), replace({User Agent}, "\\", ""),
contains({User Agent}, "\"), replace({User Agent}, "\", ""),
{User Agent})

Then, even if you want to display the original string, you can show the User Agent field in the table, but then you could include the modified version of the string as a hidden field and allow it to control the parameter controlled filter. Let me know if one of these options resolves the issue you are facing. Thank you!

3

Hello @NetTimAnon, did my previous response help resolve the issue you are facing? If not, please let me know what issues you continue to have and I can help guide you further. If we do not receive a response in 3 days, I will close out this topic. Thank you!

4

Hello Dylan,

Thank you for getting back to me. I really appreciate it. The escape character could appear anywhere within the user agent string. I will try one of the suggestions you have provided and let you know your it goes.

Thanks,

NetTimAnon

1 Like
5

Hello @NetTimAnon, let me know if you have any further questions on this! If you run into any issues when testing, I can help you resolve them. Thank you!