How to return multiple lines of a dataset with a calculated field

Hello Community!

Long time reader, first time poster…

I’m hoping someone can suggest a way I can use a calculated field and filters to returns results from a dataset that uses multiple rows for holding the data.

The dataset in question holds information about queries raised in a query management platform. Each row contains some duplicate information related to the query but unique information related to the users involved.

The table below (excuse the formatting) hopefully shows the problem…

row|query id|query creator org|query participant org|query participant email|
1|1001|Alpha Inv|Alpha Inv|person1@alpha.com|
2|1001|Alpha Inv|Alpha Inv|person2@alpha.com|
3|1001|Alpha Inv|Delta |person1@delta.com|
4|1001|Alpha Inv|Delta |person2@delta.com|
5|1002|Beta Inc|Beta Inc|person1@beta.com|
6|1002|Beta Inc|Delta |person1@delta.com|
7|1003|Charlie Plc|Charlie Plc|person1@charlie.com|
8|1003|Charlie Plc|Charlie Plc|person2@charlie.com|
9|1003|Charlie Plc|Epsilon |person1@epsilon.com|

I’m trying to create visuals in QuickSight that, for example, show all the query participant email addresses where Delta organisation was involved. Applying a filter to only show the Delta org would remove query id 1003 entirely (no-one from Delta was involved at any point), and only return rows 3, 4 and 6. I’d lose rows 1, 2 and 5.

Another example would be to create a pie chart to show all the query participant email addresses that person1@delta has spoken to by number of queries. I’d need to find a way to return all the email addresses for every query id that person1@delta was a participant in.

Apologies if i’ve not explained it very well. In SQL for example i could create a temp table with a list of query ID’s that involved a particular person, then run another query that extracted all the email addresses where the query id in the original table matched the query id in the temp table. I’m sure there’s a way using left joins and right joins to do the same thing but i can’t work it out in quicksight.

Thanks in advance!

Hey @Nick_Milnes_1 , welcome to the Quicksight community!

If I understand you correctly I believe you should be able to attain this by using an ifelse statement. Let me know if the following gets you what you are looking for:

ifelse(
contains({query creator org} , "Delta" ) = True OR
contains({query participant org} , "Delta" ) = True OR
contains({query participant email} , "@delta" ) = True, 
{query participant email}, 
Null)
1 Like

Hi Duncan, thanks for taking the time to review and suggest.

I do not think those ifelse statements would work as i am hoping to be able to extract rows where Delta isn’t listed in any of the fields.

Using the dataset example, if i wanted to show all email addresses where person1@delta engaged based on the query id, then it should return the following:-

|1001|Alpha Inv|Alpha Inv|person1@alpha.com|
|1001|Alpha Inv|Alpha Inv|person2@alpha.com|
|1001|Alpha Inv|Delta |person1@delta.com|
|1001|Alpha Inv|Delta |person2@delta.com|
|1002|Beta Inc|Beta Inc|person1@beta.com|
|1002|Beta Inc|Delta |person1@delta.com|

The reason is person1@delta was involved in query 1001 and 1002, and therefore spoke to person1 and person2 at alpha, as well as person2@delta, and person1@beta.

Using the ifelse logic, i think it would only return half of those results.

@Nick_Milnes_1 ,

Have a look at the dashboard based on your sample how-to-return-multiple-lines-of-a-dataset-with-a-calculated-field

Filtering the data will not help as it filters the rows which do not match. In this scenario we just hide the data using the similar technique which has been done for dates ( https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Year-over-Year-Table-with-Filtering-aka-Date-Hider-)

2 Likes

Hi Koushik,

Yes, this is it! Thank you! I can see a combination of calculated fields and filters are in use, which is exactly what i was struggling with.

That’s brilliant, thank you.