4 Replies Latest reply on Nov 27, 2018 10:38 AM by Shivam Agrawal

    How to create Calculated field based on Multiple Filters on Same Column ?

    Shivam Agrawal

      Hello All,

       

      Need help.

       

      My Data looks like this:

          

      IDFIELDNAMENEWVALUEUPDATE_DATE
      PR0174007state59/5/2017 5:25
      PR0174007due_date6/15/2017 8:196/6/2017 4:31
      PR0174007due_date6/29/2017 8:1920/06/2017 05:30:33
      PR0174007state20222/06/2017 05:40:36
      PR0174007due_date7/14/2017 8:1927/06/2017 05:15:42
      PR0174007due_date7/31/2017 8:1927/06/2017 07:57:41
      PR0174007state527/06/2017 07:57:41
      PR0174007due_date8/25/2017 8:1927/07/2017 10:17:12
      PR0174007state20230/07/2017 08:00:36
      PR0174007due_date9/30/2017 8:191/8/2017 5:33
      PR0174007state51/8/2017 5:33
      PR0174007state20230/08/2017 11:10:40
      PR0174007state60230/08/2017 11:13:22
      PR0174007due_date12/31/2017 8:1930/08/2017 11:21:29
      PR0174007cmdb_ci775b626ef810220061e993c41115cf587/11/2017 7:43
      PR0174007due_date3/31/2018 8:197/11/2017 7:43
      PR0174007state60111/1/2018 12:53
      PR0174007state322/02/2018 07:19:08

       

      Similarly i have data of multiple IDs in the same datasource in the similar fashion.

       

      My Problem:

       

      For Each ID, I want 2 information -

       

       

      1) what is the max time in NEWVALUE field when Fieldname is due_date   2) What is last NEWVALUE when FIELDNAME is state.

       

      For Ex in this Case: Info 1) will be 3/31/2018 8:19 (Maximum for due_date) and 2) will be 601 (Last value for state)

       

      Output:

       

       

         

      IDMAX_DUE_DATESTATE
      PR01740073/31/2018 8:19601
      PR01749993/11/2018 7:09501

       

       

      I have tried multiple ways of creating calculated field but unable to work because of 2 different filters on same column (duplicating the dimension is also didn't work).