Quicksight Q query challange

Question & Answer
, ,
1

I have a dataset containing shipment details. There are fields named destination city and destination state along with many others.

  • Destination city can have values ‘Vancouver’, ‘Vancouver IMS’, ‘Vancouver ETC’. When a user asks the question “ shipments heading to Vancouver” the expectation is that it should bring all the records from 3 cities mentioned above (‘Vancouver’, ‘Vancouver IMS’, ‘Vancouver ETC’). However, Quicksight Q only brings shipments to Vancouver. I tried to add a field value synonym but it doesn’t allow me to add the same synonym for multiple values. I added the synonym ‘Vancouver’ to ‘Vancouver IMS’ but cannot add it to ‘Vancouver ETC’.

A user might also ask a question like “shipments heading to Vancouver, BC’ where Vancouver is a city and BC is a state. I created a calculated field by concatenating city and state from the dataset. However, the results are not consistent. Quicksignt Q goes to the field Destination city instead of this new calculated field (named destination)

2

Hello @pankaj_kushwaha, welcome to the Quicksight community !

Could you instead try using the the contains function?

3

Hello @pankaj_kushwaha ! If this function worked for you could you mark my post above as a solution to help the community or let me know if you are still having trouble with this.

4

Hi @duncan - Thanks for the suggestion. It worked for me. I used the following code

ifelse(contains({Destination City}, “vancouver”, CASE_INSENSITIVE), “VANCOUVER”,{Destination City} )

1 Like