QuickSight query to return specified cirteria from multiple columns in a table

Question & Answer
, , , , ,
1

There are 12 columns of data tags that have been exported out of another program. The tags represent items and milestones. Example below;

sample table of tags
sample table of tags1324×410 20.2 KB

I want to create a control parameter per item and per milestone to filter out each row that has the tag as true. I understand how to create a control but I am struggling with the filter part. I believe if I had one SQL query I could replace the values myself, however when I write the queries I am getting errors and this is because I am new to the syntax of QuickSight. I envision having a new column for each item but I am not sure the best way to structure it.

I also have some cases where rows may have multiple values for example Widget1 and Widget2 and will need to provide a count per widget despite there being one row, but once I have the syntax and control figured out I am not worried about that capability in the visualization.

My question is how to write the SQl query to return all rows with true values for 'WidgetA" from those 12 columns. I think the rest I can figure out… Thanks!

2

Hi @anneshie

Thanks for posting your question!

To filter rows based on specific tags like WidgetA in QuickSight using SQL, you can write a query that checks each of the 12 columns for the presence of the tag.

Example:(Syntax may vary)

SELECT *
FROM tags_table
WHERE 
    Labels/Tags_01 = 'WidgetA' OR
    Labels/Tags_02 = 'WidgetA' OR
    Labels/Tags_03 = 'WidgetA' OR
    Labels/Tags_04 = 'WidgetA' OR
    Labels/Tags_05 = 'WidgetA' OR
    Labels/Tags_06 = 'WidgetA' OR
    Labels/Tags_07 = 'WidgetA' OR
    Labels/Tags_08 = 'WidgetA' OR
    Labels/Tags_09 = 'WidgetA' OR
    Labels/Tags_10 = 'WidgetA' OR
    Labels/Tags_11 = 'WidgetA' OR
    Labels/Tags_12 = 'WidgetA'
1 Like
3

Thank you, in Quicksight I am not able to use SELECT, also from is not needed since it is working directly in SPICE and already has an awareness of the table. I am trying to use ifelse, but its throwing an error.

4

I was able to create a calculated field for the first widget;

ifelse
(
{Labels/ Tags01}=‘WidgetA’ OR
{Labels/ Tags02}=‘WidgetA’ OR
{Labels/ Tags03}=‘WidgetA’ OR
{Labels/ Tags04}=‘WidgetA’ OR
{Labels/ Tags05}=‘WidgetA’ OR
{Labels/ Tags06}=‘WidgetA’ OR
{Labels/ Tags07}=‘WidgetA’ OR
{Labels/ Tags08}=‘WidgetA’ OR
{Labels/ Tags09}=‘WidgetA’ OR
{Labels/ Tags10}=‘WidgetA’ OR
{Labels/ Tags11}=‘WidgetA’ OR
{Labels/ Tags12}=‘WidgetA’
,
‘W-A’,
NULL
)

But when I try and add more widgets to the calculation filed, it throws an error. Ideally I want to be able to identify which widget in a drop down filter using a parameter and filter configuration. Still learning!

5

Multiple Column ifelse statement
Still unable to create a Quicksight calculated field using ifelse.
Anyone???

6

Hi @anneshie,
It’s been awhile since last communication took place on this thread, are you still working on this or were you able to find a work around?

In regards to your last screenshot posted, did you try without the commas between each ‘or’ statement?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

7

I am still unable to save and close without errors. So, no, I am still stuck…

8

Oh I did remove the commas and got it working! Thank you so very much!!!

1 Like