I’m working on a calculated field in AWS QuickSight and need to extract specific parts of a string stored in the array_tags field. The field contains values like this:
[ “Ticket”, “2190696”, “TCK:2190696”, “O:TKT:REQID:nicholes”, “O:TKT:OWNID:halsellm”, “O:TKT:BILLID:BPR”, “O:TKT:DEPTID:LA”, “O:TKT:PROVID:637229245:Align Chiropractic”, “S:TKT:RATEDBY:nicholes” ]
I need to extract the following values:
The value after O:TKT:BILLID: (e.g., BPR)
The value after O:TKT:DEPTID: (e.g., LA)
The value after S:TKT:RATEDBY: (e.g., nicholes)
I tried using functions like locate() and substring() but encountered syntax errors. Could anyone provide the correct syntax or method to extract these values efficiently within AWS QuickSight?
Hello @Deepshikha1, would you be able to show the calculation you wrote that uses the locate() and substring() functions? My initial thought is that those aggregation types will cause errors because they are meant for a string value when you are using an array of string values. Is there any way you can break this out into multiple columns in your dataset? I think that would function a lot easier in QuickSight.
Hello @DylanM,
Yes, you are right these aggregation types is causing errors. But the requirement is to extract these strings in a single row, but for instance you can share me the formula for calculated field which can break this out into multiple columns.
Hello @Giridhar.Prabhu, if the dataset is stored in QuickSight SPICE, we can use parseJson when we are preparing a data set, but not in calculated fields during analysis.
Hello @Deepshikha1, I believe managing this within the database by creating an alternate view or editing it with custom SQL in QuickSight is going to be the required route to manage this. Do you have the ability to write changes to the database view or is there someone on your team that can write the SQL to transform the data?
Hello @Deepshikha1, were you able to find a way to resolve this on the dataset rather than through a calculated field? Please let me know if you have any remaining questions and I will be happy to guide you further. If we do not hear back from you in 3 days, I will archive this topic. Thank you!
Hello @Deepshikha1, since we have not heard back from you, I will archive this topic. If you need further assistance, please post a new question and include a link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!