8 Replies Latest reply: Jun 27, 2016 3:02 AM by Hans de Vries RSS

    Tried max() and Group by in combination with right join...

    Hans de Vries

      Hi Everyone,

       

      In the table below, you see cumlative amounts, resulting from financial mutations. This is the original table.

      Because I want to use intervalmatch(), I would like to translate this table into a much more simpe table.

      When there are more mutations on one day, I would like to keep the mutation for the highest point of time for that day - the rest should go. At the same time, I want to translate the complex day to a start- and ending date for the period in which that mutation is valid.

      Sometimes, start and end date will be equal. The end date for the most recent mutation should be today().

       

      Original:

           

      Dossier#Claim#RankingCreation_dateCumulative_amount
      1000110001-1701-1-2015 17:15:32€ 0,00
      1000110001-1605-1-2015 13:10:11€ 15.000,00
      1000110001-1505-1-2015 14:20:17€ 14.000,00
      1000110001-1417-1-2015 07:10:09€ 13.000,00
      1000110001-1317-1-2015 07:10:35€ 17.000,00
      1000110001-1217-1-2015 09:16:51€ 14.000,00
      1000110001-1118-1-2015 20:08:00€ 11.000,00
      1000210002-1114-3-2016 16:15:00€ 18.000,00

       

       

      Desired result, after translation (both Ranking and Creation date can be dropped):

       

          

      Dossier#Claim#Start dateEnd dateCumulative_amount
      1000110001-101-1-201504-1-2015€ 0,00
      1000110001-105-1-201516-1-2015€ 14.000,00
      1000110001-117-1-201517-1-2015€ 14.000,00
      1000110001-118-1-201523-6-2016€ 11.000,00
      1000210002-114-3-201623-6-2016€ 18.000,00

       

      I hope I made it clear... Thoughts anyone? And yes, I NEED to do this in the loading script...

        • Re: Tried max() and Group by in combination with right join...
          Stefan Wühl

          Try something like

           

          Set TimeStampFormat ='DD-M-YYYY hh:mm:ss';
          
          
          Dossiers:
          LOAD *INLINE [
          Dossier# Claim# Ranking Creation_date Cumulative_amount
          10001 10001-1 7 01-1-2015 17:15:32 € 0,00
          10001 10001-1 6 05-1-2015 13:10:11 € 15.000,00
          10001 10001-1 5 05-1-2015 14:20:17 € 14.000,00
          10001 10001-1 4 17-1-2015 07:10:09 € 13.000,00
          10001 10001-1 3 17-1-2015 07:10:35 € 17.000,00
          10001 10001-1 2 17-1-2015 09:16:51 € 14.000,00
          10001 10001-1 1 18-1-2015 20:08:00 € 11.000,00
          10002 10002-1 1 14-3-2016 16:15:00 € 18.000,00
          ] (delimiter is '\t');
          
          
          LOAD *, If(previous(Claim#)<> Claim#, Today(), Date(previous(StartDate)-1)) as EndDate;
          LOAD Dossier#, Claim#, Date(Floor(Max(Creation_date)))as StartDate, FirstSortedValue(Cumulative_amount,-Creation_date) as Cumulative_amount
          RESIDENT Dossiers
          GROUP BY Dossier#, Claim#, DayName(Creation_date)
          ORDER BY Creation_date desc;
          
          
          DROP TABLE Dossiers;
          

           

           

          Dossier# Claim# Cumulative_amount StartDate EndDate
          1000110001-1€ 0,0001-1-201504-1-2015
          1000110001-1€ 11.000,0018-1-201520-6-2016
          1000110001-1€ 14.000,0005-1-201516-1-2015
          1000110001-1€ 14.000,0017-1-201517-1-2015
          1000210002-1€ 18.000,0014-3-201620-6-2016
            • Re: Tried max() and Group by in combination with right join...
              Hans de Vries

              Thanks for your swift reply, Stefan!

               

              Will try this and get back to you!

              • Re: Tried max() and Group by in combination with right join...
                Hans de Vries

                Hmm, tried it..

                I get equal end dates (all today()) and I believe in your result, there is a little mistake.. I am missing the today() end date in Claim # 10001-1...

                  • Re: Tried max() and Group by in combination with right join...
                    Stefan Wühl

                    Have you tried to run my sample script? Or did you modify the script to adapt to your source?

                     

                    If you get end date today() for all records, I think we need to adapt the condition that checks on a change in Claim#.

                     

                    Edit: And there is a today() end date in my sample table attached to my previous post, it's just the I added the script to a QVW already opened for some time and thus today() returned Jun 20.

                      • Re: Tried max() and Group by in combination with right join...
                        Hans de Vries

                        i adapted your script for mine. Here's my source script:

                         

                        Temp_1:

                        LOAD

                            %DossierNr,

                            %ClaimVlgnr                    as ClaimVlgnr,

                            Ranking,

                            [Reserve regelnr.],

                            [Res. aanmaakdatum],

                            [Reserve bedrag in €],

                            [Reserve status]

                        FROM

                        $(vQVDfolder)QVD Reserveregels_WBF.qvd

                        (qvd)

                        where [Reserve status] = 'Boeking geslaagd'

                        ;

                         

                        // sommeer losse reserves in regelnummers..

                         

                        right join(Temp_1)

                        LOAD

                            %DossierNr,

                            ClaimVlgnr,

                            Ranking,

                            min([Reserve regelnr.])        as [Reserve regelnr.],

                            only([Res. aanmaakdatum])    as [Res. aanmaakdatum],

                            only([Reserve status])         as [Reserve status],

                            sum([Reserve bedrag in €])    as [Reserve bedrag in €]

                        Resident Temp_1 group by %DossierNr, ClaimVlgnr, Ranking;   

                         

                        // kies stand reserve voor 1 dag, nl. op het meest late tijdstip van die dag..

                         

                        Result:

                        LOAD *,

                            if(Previous(ClaimVlgnr) <> ClaimVlgnr, Today(), date(previous([Datum start]-1))) as [Datum Einde];

                            Load

                            %DossierNr,

                            ClaimVlgnr,

                            date(floor(Max([Res. aanmaakdatum]))) as [Datum start],

                            FirstSortedValue([Reserve bedrag in €], -[Res. aanmaakdatum]) as [Reserve bedrag in €]

                            Resident Temp_1 group by %DossierNr, ClaimVlgnr, DayName([Res. aanmaakdatum])

                            order by [Res. aanmaakdatum] desc;

                         

                        DROP TABLE Temp_1; 

                         

                         

                        exit script;