5 Replies Latest reply: Sep 23, 2016 3:16 PM by Marco Wedel RSS

    max(date) for each client computer

    Marcus Schulz

      Good morning everybody,

       

      I hope someone can give me a hint, how I can add an new field to my data model that shows the highest compliance date for a client computer. I found several ideas but I could not find the right solution for my issue...

       

      I import a number of daily built raw-data tables (source is csv) into a .qvd and afterwards load them into my application. My challenge is now to find the highest date that is found in a certain field and add it to a new table that is linked to my data model via ClientID.


      The Client ID appears several times with different LastComplianceTime-values (including NULL). I want to keep the values for creating data history.

      Now I need a function that produces a new field called "MaxLastComplianceTime" that is filled for each client with the maximum value in the date field "LastComplianceTime". In the example below it would be "08.09.2016" for client D0000015 resp. "20.09.2016" (DD.MM.YYYY) for client D0000016.

       

      rawData:

      ClientIDLastComplianceTime
      D0000015
      D000001508.09.2016
      D000001508.09.2016
      D000001506.09.2016
      D000001505.09.2016
      D000001620.09.2016
      D000001613.09.2016
      D000001630.08.2016
      D000001630.08.2016
      D0000016

       

      Desired result:

      MaxComplianceTime:

      ClientIDMaxLastComplianceTime
      D000001508.09.2016
      D000001620.09.2016

       

      I appreciate every approach!

       

      Thanks in advance for your support!

       

      Greetings, Marcus