3 Replies Latest reply on Jul 24, 2018 6:20 PM by lei.chen.0

    Repeat values within time range in single table

    Tristan Elgar

      I'm having an issue working out how to approach this problem with Tableau, where I'd be able to do it pretty easily in SQL with a sub-query.

       

      It's basically a count of repeat faults per "Service ID" within a time/date range, e.g. where time between "resolution date" of fault minus "creation date" of the next fault on the same "Service ID" is <=14 days.

      Relevant table columns look something like this:

         id_number   |   service_id    |   restore_time               |   creation_time

      -------------------|--------------------|-------------------------------|------------------------------

      12345             | 9876               | 2018-04-01 10:00:00   | 2018-03-20 08:00:00 

      15462             | 9876               | 2018-03-17 15:00:00   | 2018-03-01 19:00:00 

      23481             | 58741G          | 2018-03-17 18:00:00   | 2018-02-28 05:00:00

      34897             | 9876               | 2018-02-12 02:00:00   | 2018-02-11 23:00:00

       

       

      So, in SQL I'd do something like:

      SELECT COUNT t1.id_number

      FROM fault_table t1, fault_table t2

      WHERE t1.service_id = t2.service_id

      AND t1.id_number <> t2.id_number

      AND (t2.restore_time - t1.creation_time) > 0

      AND (t2.restore_time - t1.creation_time) <= 20160      /* 14 days x 24 hours x 60 minutes = 20160 */

       

      So running this on the table above should give me count of 1, where creation_time of 12345 is < +14 days and >0 days from 15462.

      23481 has a different service_id, so that's not counted, and 34897 is outside the allowed time differences.

       

      I think the solution might be via a pivot in Tableau Prep, but I'm not experienced with Prep and I haven't been able to find any solutions to similar problems online.

      Any ideas?