Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

m_perreault
Contributor II

Date's Reformatted Following a Where Clause

Hi All,


I am loading data from a QVD and when I load it as is my dates are formatted 'MM/DD/YYYY' on the Front End.  However when I use a where clause in the load such as Where OrderDate = '04/30/2018'.  My dates become formatted as numbers in the front end so '04/30/2018' becomes 43220.  Does anyone know why adding a where clause would change the formatting of the date?

Thanks,
Mark

Tags (1)
4 Replies
YoussefBelloum
Esteemed Contributor

Re: Date's Reformatted Following a Where Clause

Hi,

can you attach the LOAD script (image or text) on which you're making the WHERE ?

m_perreault
Contributor II

Re: Date's Reformatted Following a Where Clause

My load script is just

TestTable:

Load *

From [($(vQVDPath)Test.qvd)] (qvd)

Where OrderDate = '04/30/2018';

I have also tried using the following

Where Date(OrderDate,'MM/DD/YYYY') = '04/30/2018';

Where Date(Floor(OrderDate),'MM/DD/YYYY') = '04/30/2018';


And am still seeing the date be changed to a num whenever I use the Where Clause formatted as a Date when I do not.


Thanks,

Mark

YoussefBelloum
Esteemed Contributor

Re: Date's Reformatted Following a Where Clause

did you try this:

TestTable:

Load

Date(OrderDate ,'MM/DD/YYYY') as OrderDate,

.

.

.

From [($(vQVDPath)Test.qvd)] (qvd)

Where Date(OrderDate,'MM/DD/YYYY') = '04/30/2018';


also, what is the format of this OrderDate field ? and what is your Date system variable format ?


m_perreault
Contributor II

Re: Date's Reformatted Following a Where Clause

My Date System Variable Format is the Default 'M/D/YYYY'.

I have tried using

TestTable:

Load

Date(OrderDate ,'MM/DD/YYYY') as OrderDate,

.

.

.

From [($(vQVDPath)Test.qvd)] (qvd)

Where Date(OrderDate,'MM/DD/YYYY') = '04/30/2018';

Which will yield the desired formatting in the Front End.  I was more just curious as to why the addition of the Where Clause would change the formatting from Date to Num.  I believe the format of the OrderDate field is a timestamp which has been formatted Date(OrderDate,'MM/DD/YYYY') but has not had Date(Floor(OrderDate,'MM/DD/YYYY') which I am wondering if this is having an effect.

Thanks,

Mark

Community Browser