1 Reply Latest reply on Dec 19, 2017 3:32 PM by swaroop.gantela

    Effective way to create an unique ID and filter on it?

    Jakob Erikstad

      Hi.

       

      I'm experiencing extreme query times (4 min), and I believe that is (partly) due to the way I filter on a calculated field, and the way the calculated field is created. I'm relatively new to Tableau so some help would be much appreciated.

       

      Data: ca 400 000 rows x 50 columns.

       

      Relevant columns: Customer_ID, Car_ID_1, Car_ID_2, Contract_ID, Date. A customer can be combined with several cars and a car can be combined with several customers.

       

      Target: Create an unique ID field for each customer/car relation, filter on the first date entry with that ID, and sum number of occurrences within a given interval.

       

      Complications: Car_ID_1 and Car_ID_2 can be NULL.

       

      Current attempt:  If Car_ID_1 is NULL, it can many times be found within same contract and if not I use Car_ID_2 in its place(appendix1). Both being NULL at the same time rarely happens(and if they can be ignored).
                                    I create the ID (see appendix1 and appendix2) and then create a filter (appendix3)

       

      Appendix1 - Car_ID

       

      IF ISNULL([Car_ID_1])

      THEN

      IF ISNULL({ FIXED [Car_ID_2],[Contract_ID])}):Max(Car_ID_1)})

      then

      [Car_ID_2]

      ELSE

      { FIXED [Car_ID_2],[Contract_ID])}):Max(Car_ID_1)}

      END

      ELSE

      [Car_ID_1]

      END

       

      Appendix 2 - Cust_Car_ID

       

      STR([Customer_ID])+"/"+[Car_ID]

       

      Appendix 3 - filter

       

      IF { FIXED [Cust_Car_ID]: MIN([Date])} =[Date]

      THEN 1 ELSE 0 END

       

       

      Appendix 4 - example data

       

      Customer_IDCar_ID_1Car_ID_2Date
      ANULL1106/01/2011
      A11118/01/2011
      A11119/01/2011
      B22218/01/2011
      C35507/01/2011
      C46626/01/2011
      D46627/01/2012
      E5NULL19/01/2011
      F68825/01/2011