Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Multiple occurences of the same row in a table

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.

Tags (2)
4 Replies
lironbaram
Honored Contributor II

Date & Makedate question

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
Valued Contributor

Date & Makedate question

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.

Not applicable

Date & Makedate question

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.

Not applicable

Re: Mutliple occurences pb in a table

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 ?

Community Browser