12 Replies Latest reply: Jun 15, 2016 9:37 AM by Bharath Tinnaluru RSS

    Max(Datum)

    Hans de Vries

      What I need is to simpify this table.. In the original table, there are (potentially) many transactions on one day, hence the hh:mm:ss in the date. What I want is in the resulting table: when there are more than 1 transactions on one day, I need to keep the most recent transaction for that day, with the corresponding reserve amount. Days can then lose the hh:mm:ss extensions, so I get one final transaction per whole day.

       

      Again, I think this should be accomplished with max() and group by.. But how? I did a few attempts, but couldn't get it to work.

       

      See example below:

       

      Original:

         

      %DossierNr%ClaimVlgnrDatumReserve
      990019914990019914-12016-06-06 20:25:31€ 239.419,78
      990019914990019914-12016-06-06 20:21:01€ 240.938,57
      990019914990019914-12016-06-06 20:14:14€ 242.325,84
      990019914990019914-12016-06-06 19:56:03€ 282.325,84
      990019914990019914-12016-06-06 19:52:32€ 283.006,66
      990019914990019914-12016-04-17 21:34:13€ 285.000,85
      990019914990019914-12016-04-04 11:10:47€ 286.000,85
      990019914990019914-12016-03-30 21:02:23€ 291.500,00
      990019914990019914-12016-03-04 09:16:06€ 295.500,00
      990019914990019914-12016-03-02 14:10:48€ 300.000,00
      990019914990019914-12016-02-12 08:55:16€ 11.150,29
      990019914990019914-12015-04-16 10:26:52€ 14.500,00

       

      Desired result:

         

      %DossierNr%ClaimVlgnrDatumReserve
      990019914990019914-12016-06-06€ 239.419,78
      990019914990019914-12016-04-17€ 285.000,85
      990019914990019914-12016-04-04€ 286.000,85
      990019914990019914-12016-03-30€ 291.500,00
      990019914990019914-12016-03-04€ 295.500,00
      990019914990019914-12016-03-02€ 300.000,00
      990019914990019914-12016-02-12€ 11.150,29
      990019914990019914-12015-04-16€ 14.500,00
        • Re: Max(Datum)
          Sunny Talwar

          Do you need this in the script or front end?

          • Re: Max(Datum)
            mayilvahanan ramasamy

            Hi


            Try like this

             

            Test:

            LOAD *, Floor(Datum) as Date INLINE [

                %DossierNr, %ClaimVlgnr, Datum, Reserve

                990019914, 990019914-1, 2016-06-06 20:25:31, "€ 239.419,78"

                990019914, 990019914-1, 2016-06-06 20:21:01, "€ 240.938,57"

                990019914, 990019914-1, 2016-06-06 20:14:14, "€ 242.325,84"

                990019914, 990019914-1, 2016-06-06 19:56:03, "€ 282.325,84"

                990019914, 990019914-1, 2016-06-06 19:52:32, "€ 283.006,66"

                990019914, 990019914-1, 2016-04-17 21:34:13, "€ 285.000,85"

                990019914, 990019914-1, 2016-04-04 11:10:47, "€ 286.000,85"

                990019914, 990019914-1, 2016-03-30 21:02:23, "€ 291.500,00"

                990019914, 990019914-1, 2016-03-04 09:16:06, "€ 295.500,00"

                990019914, 990019914-1, 2016-03-02 14:10:48, "€ 300.000,00"

                990019914, 990019914-1, 2016-02-12 08:55:16, "€ 11.150,29"

                990019914, 990019914-1, 2015-04-16 10:26:52, "€ 14.500,00"

            ];

            Left Join

            LOAD Date, Max(Datum) as MaxDatum Resident Test

            Group by Date;

            Final:

            LOAD *, if(Datum = MaxDatum, 1, 0) As Flag Resident Test;

             

            DROP Table Test;

             

            Datum %ClaimVlgnr %DossierNr Flag =Only({<Flag = {1}>}Reserve)
            2016-06-06 20:25:31990019914-19900199141€ 239.419,78
            2016-04-17 21:34:13990019914-19900199141€ 285.000,85
            2016-04-04 11:10:47990019914-19900199141€ 286.000,85
            2016-03-30 21:02:23990019914-19900199141€ 291.500,00
            2016-03-04 09:16:06990019914-19900199141€ 295.500,00
            2016-03-02 14:10:48990019914-19900199141€ 300.000,00
            2016-02-12 08:55:16990019914-19900199141€ 11.150,29
            2015-04-16 10:26:52990019914-19900199141€ 14.500,00