In my dataset, we have a column called ‘exception comments.’ By default, this column includes both the auditor’s name (who posted the comments) and a ** date and timestamp**. However, one of my clients wants to remove the auditor’s name and timestamp from the report. Could you please guide me on how to achieve this?
Example - Documentation is not clear, need to re upload the document - Alex George Mar 9, 2022, 2:25PM
Hi @tdr_Dinesh
i guess the structure is different every timeborbisbit always “comment - name time”?
BR
Hi @ErikG ,
Yes, always it will be in below format, in rare scenarios the structure is different.
Comments - Auditor name - date and time stamp
Thank you!
You could use
to find the first “-” and use
to keep just the comment part.
left({exception comments},locate({exception comments},"-")-1)
But will only work as long as no “-” is within the comment itself.
Thank you @ErikG ,
I would like to check is there is any other possibilities to achieve this when the structure is different.
EX: Comments - Comments - Auditor name - date and time stamp- comments
What datasource type are you using?
Ok. Then you could use SQL functions as well. But it would be hard to tell what is a comment and what is e.g. a name part, right?
Any chance to find a logic?
Hi @ErikG ,
Unfortunately, we still haven’t found the solution. As you mentioned, it works for the structure ‘comment - name time.’ Not working when the structure is different.
Thank you!
If you have the auditors name in another column you could use locate to find this information. Then mask it probably with a replace function. Otherwise probably look into redacting pii information in the source itself and create a new file , then use it to visualize in QuickSight. for eg ( Translate, redact, and analyze text using SQL functions with Amazon Athena, Amazon Translate, and Amazon Comprehend | AWS Machine Learning Blog )