4 Replies Latest reply: Aug 17, 2011 8:51 AM by Jean-Philippe Gembert RSS

    Multiple occurences of the same row in a table

    Jean-Philippe Gembert

      Hi everyone,

       

       

      I've got a question about the Date or Makedate function in QV.

       

      In my script, I create a table like this :

       

      ...

       

      INNER JOIN

       

           LOAD IF( LEFT(TRIM(GLSUB), 1) = 'P',

                    TRIM(GLSUB),

                    TRIM(GLSUB) & '1' )                        AS OPERATIONS_ID,

                TRIM(GLOBJ)                                    AS ENGAGE_CATEGORIE_1,

                GLAA/100                                       AS REALISE_JDE_MONTANT_HT,

                DATE(MAKEDATE(1900 + MID(GLDGJ,1,LEN(GLDGJ)-3))

                     + MID(GLDGJ,LEN(GLDGJ)-2,3)-1)            AS REALISE_JDE_DATE_GL,          

                TRIM(GLSBL)                                    AS REALISE_JDE_SOUS_LIVRE,

                GLLT                                           AS REALISE_JDE_JOURNAL,

                ROWNO()                                        AS REALISE_JDE_ROWNO

          FROM $(v_JDE_QVD_Path)\F0911.qvd (qvd)

         WHERE MATCH(GLOBJ, '1511', '1512', '1513', '1514')

           AND MATCH(GLLT, 'AA', 'AX')

               ;

       

      INNER JOIN

       

      ...

       

       

      I have to convert a Julian date stored in my JD-Edwards data source into a Gregorian with DATE & MAKEDATE.

       

      I have noticed something strange :

       

      Without the ROWNO() in my resultset, the table will contain only one occurrence of identical data sets in my source

       

      It's like DATE or MAKEDATE aggregates data (a sort of GROUP BY)

       

      Is it normal or did I missed something ?

       

      Thank you all in advance.

       

      Oomingmak.

        • Date & Makedate question
          Liron Baram

          hei

           

           

          you sholud know that if you you table box and all the vlues in two rows are the same

           

           

          it will show only one row,

           

          if you want to check how many rows are in the table

           

           

          build a table box with the fields $(Tables) ,$(Rows)

           

          which are system fields that way you can know the real number of rows

          • Date & Makedate question
            Goran Korsgren

            Hi

            Just as liron said, it is more likely the normal QlikView behaviour you are experiencing, and has nothing to do with Date or MakeDate.

             

            In a table (or chart), QV automatically only shows distinct combinations of the fields involved.

             

            See my example here:

            values.JPG

            Note that in the Data table there are two identical rows.

            If you create a Table Box you will just see the unique combinations that Data has.

            But if you create a chart and display count of any of the fields you will see that there are actually more rows for that data.

              • Date & Makedate question
                Jean-Philippe Gembert

                Thank you for your answers.

                 

                I know that table visual object only shows one occurence of the same result set.

                 

                My problem is that the multiple occurences were not in the (memory) table until I placed the rowno() column in its construction. (I checked this using the method mentioned here http://qlikviewmaven.blogspot.com/2011/07/duplicate-rows-in-qlikview-table.html and a partial reload)

                 

                I identified my problem because in the visual part of the app I got a sum() in a pivot table that returned an inferior value that the one expected.

                  • Re: Mutliple occurences pb in a table
                    Jean-Philippe Gembert

                    Hi again,

                     

                     

                    I cheked once again and you were right, it has nothing to do with Date( )or Makedate() functions.

                     

                    The problem was above in the construction of my table :

                     

                    REALISE_JDE:

                        LOAD DISTINCT OPERATIONS_ID

                    RESIDENT OPERATIONS

                             ;

                     

                    INNER JOIN

                     

                         LOAD IF( LEFT(TRIM(GLSUB), 1) = 'P',

                                  TRIM(GLSUB),

                                  TRIM(GLSUB) & '1' )                        AS OPERATIONS_ID,

                              TRIM(GLOBJ)                                    AS ENGAGE_CATEGORIE_1,

                              GLAA/100                                        AS REALISE_JDE_MONTANT_HT,

                              DATE(MAKEDATE(1900 + MID(GLDGJ,1,LEN(GLDGJ)-3))

                                   + MID(GLDGJ,LEN(GLDGJ)-2,3)-1)            AS REALISE_JDE_DATE_GL,          

                              TRIM(GLSBL)                                    AS REALISE_JDE_SOUS_LIVRE,

                              GLLT                                             AS REALISE_JDE_JOURNAL

                        FROM $(v_JDE_QVD_Path)\F0911.qvd (qvd)

                       WHERE MATCH(GLOBJ, '1511', '1512', '1513', '1514')

                         AND MATCH(GLLT, 'AA', 'AX')

                             ;

                     

                    INNER JOIN

                    ...

                     

                    It seems that the initial DISTINCT impacts ALL the INNER JOIN parts.

                     

                    I don't really understand why ?

                     

                    Any Idea ?