Hi
given the table below
I would like to be able to uniquely count only the HubspotID that have consideration phase 1 and that have decision phase 0
I should therefore only count this row
digital_payment 13.677.111 digital_payment_consideration consideration 1
and the output I would need is an aggregate table like this
Hello @andreab, I believe in a situation like this, you would want to have the HubspotId on the same row as both the decision and consideration phases so you can check for the match within QuickSight. If you wanted to keep your table the way it is, you could try adding a new column in the custom SQL that queries your datasource that would be controlled by a case statement.
This isn’t exact but an idea of what you would do:
case when hubspotid in digital_payment_decision and hubspotid not in digital_payment_consideration then 1 else 0 end as only_consideration
Then if that row value contains a 1 on the hubspotid, you would know to return that row. Otherwise, QuickSight isn’t going to be able to figure it out because you are comparing values between 2 seperate rows when it is trying to check within a single row.
Let me know if this helps!
Hi @DylanM
but in custom sql how should I write the case code. it’s not clear to me because it
can’t go the way you wrote it
thanks
Hello @andreab, yeah I do not know exactly how your database is set up or how your custom SQL looks currently, so the above statement will not be exact. I would recommend looking at some case when statement documentation to ensure you are only displaying a 1 when the proper values have been checked else show a 0.
