5 Replies Latest reply: Jan 16, 2017 11:20 AM by Eduardo DImperio RSS

    Problem using Distinct

    Eduardo DImperio

      Hi people,

       

      For some reason my distinct doesnt work.

       

      TMP:

      LOAD

       

      TIMESTAMP([Entrada Desenv.],'MM/YYYY') AS DATA_SELECAO

       

      RESIDENT AVALIACAO_ANALISE

      ;

       

      DATA_SELECAO:

       

      LOAD

      distinct

      DATA_SELECAO

       

      RESIDENT TMP;

       

      DROP TABLE TMP;

       

       

      Without Distinct, it works fine

      Erro_Distinct.JPG

        • Re: Problem using Distinct
          Marcus Sommer

          Why not just using:

           

          DATA_SELECAO:

          LOAD date(floor(monthstart([Entrada Desenv.])),'MM/YYYY') AS DATA_SELECAO

          RESIDENT AVALIACAO_ANALISE;

           

          DATA_SELECAO:

          LOAD distinct DATA_SELECAO RESIDENT TMP;

          DROP TABLE TMP;

           

          with adjusting your YearMonth field because timestamp() will only format a field but it willbe remain a timestamp.

           

          - Marcus

            • Re: Problem using Distinct
              Eduardo DImperio

              Hi Marcus !

               

              It works and i think what's happen now.

               

              A Timestamp is just visual. The day of my date field still there even if i use a timestamp MMYYY and for that reason distinct doesnt works. Right?

                • Re: Problem using Distinct
                  Anna fuksa

                  you need to use function floor like Marcus show you other wise it will keep all valur in a field (for example date as timestamp is save as 41909.987 and even if it will show only 27/09/2014 it will still save as '41909.987'and date for example 41908.087 will be as other date even if is 26/09/2014 as well. even if its the same month

                   

                  you can use as well function MonthName

                  TMP:

                  LOAD

                   

                  MonthName(date([Entrada Desenv.])) AS DATA_SELECAO

                   

                  RESIDENT AVALIACAO_ANALISE

                  • Re: Problem using Distinct
                    Marcus Sommer

                    Normally the distinct keyword itself shouldn't lead to an error in this case but within your first approach there shouldn't be a table DATA_SELECAO.

                    The reason for this is that this table has the same data-structure like the table TMP and will be automatically concateneted with TMP and afterwards TMP is dropped - so that there would be no DATA_SELECAO and TMP tables within the datamodel. And this might be your displayed error - because the field for it is missing.

                     

                    - Marcus