Hi. I’m new to QuickSight, and I’m hoping someone can help.
I have a dataset imported of login/logout data that has various computer names, username, date, and timespent. I want to create a control for a visualization that allows the user to select the lab name, and see all the sessions for those computer names.
My computer names are all prefixed with the building and room number and then an individual number such as:
abc12301
abc12302
xyz20201
xyz20202
Since I want to pull all the computer names, for instance, that start with abc123, I know I’ll have to use a locate or contains function, which means I need to create a calculated field, yes? Please correct me if I’m wrong. I understand the location function, but not how to integrate it into a statement that will allow me to pull all the hostnames that =1 or match the substring. How do I do this and then tie it to a control?
Greatly appreciate the help!
Vincent
Hi @VLThompson
Welcome to the Quicksight Community! Thank you for posting your question.
So, You want to allow users to filter sessions by lab (e.g., abc123), where lab identity is determined by the prefix of the computer name (abc12301, abc12302, etc.).
Step 1: Extract the Lab Prefix (Calculated Field)
Yes — you will need to create a calculated field to extract the lab name prefix from your computer_name. Assuming the lab name is always the first 6 characters (like abc123):
I would say please go to your dataset → edit dataset and then add calculated field at the dataset level so that when you add control as a dropdown you will be able to link to this field.
Cal field name - lab_name
substring({computer_name}, 0, 6)
If your prefix length varies and is separated by a known delimiter (e.g., abc123-01), then you could also use split().
Step 2: Create a Control
Now that you have a lab_name field:
- Go to Analysis → Parameters → Create a parameter called
Selected Lab.
- Set it as String and enable single select or multi-select (your choice).
- Go to Filters and add lab_name as filter on your visual → Link it to your
Selected Lab parameter.
- Add Control → Display it as a dropdown with values from the
lab_name field.
Let me know if this helps
Thank you,
Shravay
Shravay,
Thanks for the reply! I leveraged ChatGPT after I made this post, and it told me the same thing.
The odd this is when editing the dataset and adding the new calculated field, I enter the substring command exactly as you have it, but when I save it, QuickSight can’t perform the addition of the new field. No data in preview, says there are 0 rows to display. I remove the calculated field, data shows up fine. I even did a refresh on the data since it’s SPICE, and I get a data ingestion error.
The substring is pretty basic, not sure why it’s have trouble. I may have to go back to my original data files and script in the addition of the extra/needed field.
If you have any idea why this calculated field is causing the behavior, I’d appreciate it.
Thanks,
Vincent
1 Like
Hello @VLThompson
If possible could you please send a screenshot of the issue you are facing because i have implemented what i have told in the above steps so i was able to do it. It would be easier to debug if you can provide any inputs only if possible.
fyi -
Thank you,
Shravya
Hi @VLThompson , @shravya
The only issue that I see is that the substring start position is 1 and not 0. It does not complain when you pass 0 but then the results are not right
See my example below. You can see that only 3 characters are in the substring result since the start position is mentioned as 0
When I change the start to 1 things work as expected.
I guess if you make that change you should get the results you want.
1 Like
Giridhar,
That’s it! The beginning index is 1, not 0. The calculated field worked like it should when using 1.
I appreciate all the help!
Vincent
