Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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.
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 ?