topic Date issue in QlikView

Date issue

emmanueld — Wed, 31 Aug 2016 15:20:29 GMT

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 — Fri, 02 Sep 2016 10:38:00 GMT

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

emmanueld — Fri, 02 Sep 2016 11:48:18 GMT

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

emmanueld — Sat, 03 Sep 2016 15:25:11 GMT

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 — Sat, 03 Sep 2016 18:13:05 GMT

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

emmanueld — Sat, 03 Sep 2016 18:40:17 GMT

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

Gysbert_Wassenaar — Sun, 04 Sep 2016 17:13:43 GMT

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