topic Re: Mixed Dates, same column in QlikView

Mixed Dates, same column

leocattqv — Mon, 19 Oct 2015 19:44:30 GMT

Hi All,

I havent run into this before maybe you have. I have a table that gives me the creation date of the record. However, there are some date format and some number format. Any suggestions on where to lookto fix this?

COMPLAINT_NUMBER	COMPLAINT_ANALYST	COMPLAINT_STATUS	COMPLAINT_AGE	Product_Name_Comp	Product_Brand_Comp	Site_Complaint	CREATION_DATE
								6
CC-00053479	Bob Smith	Review	52	Product 1	Disney	NoSite	8/24/2015	1
CC-00053530	Bob Smith	Review	52	Product 2	Goofy	NoSite	8/24/2015	1
CC-00054168	Bob Smith	Review	49	Product 3	Mickey	NoSite	42243	1
CC-00054507	Bob Smith	Review	45	Product 4	Disney	NoSite	8/31/2015	1
CC-00054909	Bob Smith	Review	43	Product 5	Goofy	NoSite	42249	1
CC-00054959	Bob Smith	Review	43	Product 6	Mickey	NoSite	42249	1

Re: Mixed Dates, same column

Anonymous — Mon, 19 Oct 2015 19:54:00 GMT

very curious

I would define the dateformat under document Settings tab number

define Creation Date as dateformat with yourrequired date Format

does the data Comes from same source?

Re: Mixed Dates, same column

crystles — Mon, 19 Oct 2015 19:55:03 GMT

in the script, try putting the Date() function around the field.

Date(CreationDate) as Date

Re: Mixed Dates, same column

leocattqv — Mon, 19 Oct 2015 19:59:29 GMT

Yes, the data comes from the same place, it is related to a calendar, the code looks like this,

SubField([Date Originated],' ',1) AS CREATION_DATE,

Should it look like..

SubField(date([Date Originated]),' ',1) AS CREATION_DATE,

Re: Mixed Dates, same column

Anonymous — Mon, 19 Oct 2015 20:06:18 GMT

it's a good idea to define the format

but it should be like this:

date(SubField([Date Originated]),' ',1)) AS CREATION_DATE,

Re: Mixed Dates, same column

Mon, 19 Oct 2015 21:40:06 GMT

Hi Leo,

Try using the TRIM() function -> Date(Trim(SubField([Date Originated],' ',1)),'M/D/YYYY') AS CREATION_DATE,

I bet there are spaces in the data values causing QV to interpret the number as text rather than a date #

Re: Mixed Dates, same column

MarcoWedel — Mon, 19 Oct 2015 23:06:41 GMT

maybe also possible:

Date#(SubField([Date Originated],' ',1),'M/DD/YYYY') as CREATION_DATE,

Date(Date#(SubField([Date Originated],' ',1),'M/DD/YYYY')) as CREATION_DATE,

to convert it to your default DateFormat.

Or assuming your [Date Originated] field in fact is a timestamp (because you only use the part before the first blank):

Timestamp#([Date Originated],'M/DD/YYYY hh:mm:ss') as CREATION_DATE_TIME,

and in a preceding load

DayName(CREATION_DATE_TIME) as CREATION_DATE

to get both the timestamp and a date.

hope this helps

regards

Marco

Re: Mixed Dates, same column

leocattqv — Tue, 20 Oct 2015 15:56:54 GMT

that worked, thank you