Multiple occurences of the same row in a table

[#1]

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.

[lironbaram]

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

[gandalfgray]

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:

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.

[#4]

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.

[#5]

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 ?