Search code examples
sqlgroup-bytableau-api

Tableau/SQL Calculated Field With Grouping

I have a table with the following structure

id, event_name, event_date
| 1 | a | 1.1.2020 | 
| 2 | b | 3.2.2020 | 
| 3 | b | 3.2.2020 |
| 3 | b | 5.2.2020| 
| 1 | b | 31.12.2019 | 
| 2 | a | 5.1.2020 |

My goal would be to perform a grouping on the id and then I'd have to check wheter the date of an event 'a' comes before an event 'b'. If so I'd like to output 'ok' and 'error' elsewise.

In this example this would result to

id, check
| 1 | error| 
| 2 | ok |  
| 3 | ok |

Would it be possible to perform the task with a calculated field in Tableau? SQL would be also be ok!

Solution

  • Try this

       Select id, case when diff<0 then 'ok' 
   else 'error' end as status from 
   (
    Select id,

    max(case when event_name ='a' then event_date end) - 
    max(case when event_name='b' then event_date end)
   As diff
    From table group by id order by id)