6 Replies Latest reply: Sep 4, 2016 1:13 PM by Gysbert Wassenaar RSS

    Date issue

    Emmanuel Damiano

      Hi,

      I have the following table with two datetime fields.

       

      CREATE TABLE PARAMETRES.[dbo].[XLS_IMPAYES_HP1_EXCLUE](

        [ORGANISME] [char](3) NOT NULL,

        [CODE HP1] [char](4) NOT NULL,

        [DT_INSERT] [datetime] NOT NULL,

        [DT_FIN] [datetime] NOT NULL

      ) ON [PRIMARY]

       

      step 1 storage into QVD :

       

      IMPAYES_HP1_EXCLUE:

      SQL SELECT [CODE HP1] as HP1_EXCLU_IMP,

        DT_INSERT,

        DT_FIN

      FROM PARAMETRES.dbo.XLS_IMPAYES_HP1_EXCLUE;

       

      (then classic store into 0001_Reunion_Rencontre_QVD_IMPAYES_HP1_EXCLUE.qvd)

       

      step 2 load from QVD :

       

      EXCLUSION_IMPAYES:

      LOAD HP1_EXCLU_IMP as EXCLU_IMP_HP1,

          DT_INSERT as EXCLU_IMP_DT_DEB,

          DT_FIN as EXCLU_IMP_DT_FIN

      FROM

        [0001_Reunion_Rencontre_QVD_IMPAYES_HP1_EXCLUE.qvd]

      (qvd);

       

      step 3 in text object : display list of EXCLU_IMP_HP1 such as Date 1 <= current date <= Date 2

       

      =Concat({1< [EXCLU_IMP_DT_DEB]={"<=$(=$(vImpayesMoisCour))"}, [EXCLU_IMP_DT_FIN]={">=$(=$(vImpayesMoisCour))"}>} [EXCLU_IMP_HP1], ',')

       

      Step 1 works fine and stores a numeric value for each of the two dates DT_INSERT and DT_FIN

      Step 2 doesn't work as expected : the table viewer shows a numeric value in the first one and a DD/MM/YYYY hh:mm:ss value in the second one.

      Step 3 : the whole expression shows nothing.

                =$(vImpayesMoisCour) shows a DD/MM/YYYY value and the expression works if I remove the second filter. The only issue is that the second filter doesn't seem to recognize EXCLU_IMP_DT_FIN as a date field and therefore can't compare it to the expression in brackets.


      Could you please help me on this one?

      I don't see why the two fields are not interpreted or stored the same way in step 2.


      Many thanks

        • Re: Date issue
          Gysbert Wassenaar

          Regarding step 2. It seems that in step 1 one of the date fields was recognized as a date field and the other wasn't. But it's also possible the are text values as well. Add both fields to a listbox and set the Number format to 'Fixed to'. Then check if you only see right aligned numbers in the listboxes. Anything left aligned is a text value.

           

          If you need more help, post a small qlikview document that demonstrates the problem.

          • Re: Date issue
            Emmanuel Damiano

            Thank you Gysbert for your answer. Yes the two selection lists show different values, just as the table viewer : a numeric value in the first one and a DD/MM/YYYY hh:mm:ss value in the second one, while the QVD contains two right-aligned numeric values. I'll try to post a doc.

            • Re: Date issue
              Emmanuel Damiano

              please find the document I posted showing the issue : if I load my QVD once, one of the two fields is not displayed as a number in the listbox.

              What is funny is that I tried loading the QVD a second time in a concatenate load without renaming the fields, and the two additional date fields are both displayed as numbers in list boxes! But when I load them in a second table separately (still without renaming them), the issue reappears.

                • Re: Date issue
                  Gysbert Wassenaar

                  The dates all seem to be dates. Though you may want to turn into a number too like the other date fields:  floor(DT_FIN) as  EXCLU_IMP_DT_FIN

                   

                  The qvw document you posted does not contain a variable vImpayesMoisCour. So I have no idea what value it should have or how it's calculated.

                • Re: Date issue
                  Emmanuel Damiano

                  Thanks a lot Gysbert, indeed the floor function solves the problem! But I don't understand exactly why. The DT_FIN field contained precise dates with no hour or minute value, that could end up as integers on their own, so why do we need the floor function here, whereas we don't need it for DT_INSERT?

                   

                  As for vImpayesMoisCour, I haven't included it in this simplified version of my app, because it needed other tables to be loaded. I'll see if the problem is completely solved and come back to you