Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor II

Date issue


I have the following table with two datetime fields.


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

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

  [DT_INSERT] [datetime] NOT NULL,

  [DT_FIN] [datetime] NOT NULL


step 1 storage into QVD :






(then classic store into 0001_Reunion_Rencontre_QVD_IMPAYES_HP1_EXCLUE.qvd)

step 2 load from 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

Tags (1)
6 Replies

Re: Date issue

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.

talk is cheap, supply exceeds demand
New Contributor II

Re: Date issue

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.

New Contributor II

Re: Date issue

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

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.

talk is cheap, supply exceeds demand
New Contributor II

Re: Date issue

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

Re: Date issue

If it now works then the problem was different date formats. DT_FIN had formatting and the rest of the date fields didn't. That matters in set analysis expressions. See this blog post for more information: Dates in Set Analysis

talk is cheap, supply exceeds demand
Community Browser