8 Replies Latest reply: Aug 6, 2018 7:22 AM by Ernesto Alves RSS

    Buscar somente ultima data do mês com informação

    Ernesto Alves

      Bom dia,

       

      Estou com dificuldades para entender como posso pegar somente a ultima data do mês, com registro na base, para cada conta.

      Em anexo uma planilha com uma amostra. Eu preciso somente dos registros em amarelo. Observem que não é necessariamente o último dia do mês, mas sim a última data registrada.

       

      A ideia é pegar o resultado e gravar em uma tabela.

       

      Desde já agradeço pela atenção.

       

      Ernesto Alves.

        • Re: Buscar somente ultima data do mês com informação
          Paola Valenti

          Sorry, I can't speak Portuguese.

          I hope I can help you in English (or Italian, if you prefer).

           

          Load your Excel file

          Pay attention: field DATASALDO has to be a date.

          Check DateFormat at the start of the script or, in load, you can write:

          Date(Date#(DATASALDO,'YYYY-MM-DD')) as DATASALDO.

          Now you can calculate End Month Date:

          Date(Floor(MonthEnd(Date#(DATASALDO,'YYYY-MM-DD')))) as MONTHLASTDAY

           

          If you want calculate a flag in the script you can compare DATASALDO and MONTHLASTDAY (as defined before) in load:

          if(DATASALDO=MONTHLASTDAY,'T','F') as FLAG


          In front end, if you filter 'T' in field FLAG you will have only value if DATASALDO is last day of month.


          You can operate directly in front end with set analysis in your expression.

          For instance, you need only value of VALORDEB (please, assure you loaded VALORDEB as numeric) for which DATASALDO is last day of month. Then you can write:

          sum({<DATASALDO={"=(DATASALDO=MONTHLASTDAY)"}>}VALORDEB)

          if you defined MONTHLASTDAY in the script.

          If you don't have loaded MONTHLASTDAY in the script you can write in Set Analysis

          sum({<DATASALDO={"=(DATASALDO=Floor(MonthEnd(DATASALDO)))"}>}VALORDEB)


          Please try and give feedback if I understand your question.

            • Re: Buscar somente ultima data do mês com informação
              Ernesto Alves

              Hi paola,

               

              First, thank you for responding!

               

              I understood all of your explanation.

              Your idea is great, but it still does not solve my problem.

              I'll try to explain better.

              I need to run a load script from a QVD as per the example in the attached worksheet.

              In this script, check for each CODIGEMEN + CONTACTB, IF DATASALDO is the last record of THAT MONTH and then save the corresponding VALORDEB and VALORCRED in another table.

              Based on the attached example, the result of the created table would be the following:

                   

              CODIGOEMPRESACONTACTBDATASALDOVALORDEBVALORCRED
              599112017-12-308997490
              599112018-01-31029931.59
              599112018-02-28266988423.31
              599112018-03-2910835.2218478.39
              599112018-04-3030938.444156.24
              599112018-05-302124130537.63
              599112018-06-2805.10

               

              Best regards,

                • Re: Buscar somente ultima data do mês com informação
                  Paola Valenti

                  I'm sorry I misunderstood.

                  When you write CODIGEMEN + CONTACTB, do you mean CODIGOEMPRESA+CONTACTB?

                  Try this in the script:


                  Temp_aaaa:

                  LOAD *,

                      MakeDate(year(DATASALDO),month(DATASALDO),1) as MonthYear,

                  FROM [lib://YOURPATH/aaaa.xlsx]

                  (ooxml, embedded labels, table is Result);

                   

                  MonthLast:

                  LOAD distinct MonthYear,

                       CODIGOEMPRESA,

                      CONTACTB,

                       Date(max(DATASALDO)) as MonthLast

                  Resident Temp_aaaa

                  group by MonthYear,CODIGOEMPRESA,CONTACTB;

                   

                  New_aaaa:

                  LOAD CODIGOEMPRESA,

                      CONTACTB,

                      DATASALDO,

                      VALORDEB,

                      VALORCRED

                  resident Temp_aaaa

                  where exists(MonthLast,DATASALDO);

                   

                  drop table Temp_aaaa,MonthLast;

              • Re: Buscar somente ultima data do mês com informação
                Mauri Kawamura

                Boa tarde,

                 

                Acho que pode ser algo assim:

                 

                tempTeste:

                LOAD CODIGOEMPRESA,

                     CONTACTB,

                     DATASALDO,

                     Num(DATASALDO) as NumData,

                     text(Date(Num(DATASALDO),'MMM/YY')) as MesAno,

                     VALORDEB,

                     VALORCRED

                FROM

                [..\Downloads\aaaa.xlsx]

                (ooxml, embedded labels, table is Result);

                 

                 

                Left Join

                LOAD max(NumData) as NumData,

                  1 as flagMaximaData

                Resident tempTeste

                Group by MesAno;

                 

                 

                NoConcatenate

                Final:

                LOAD *

                Resident tempTeste

                Where flagMaximaData= 1;

                 

                 

                DROP Table tempTeste;

                 

                Att,