Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
leocattqv
Creator
Creator

Mixed Dates, same column

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_NUMBERCOMPLAINT_ANALYSTCOMPLAINT_STATUSCOMPLAINT_AGEProduct_Name_CompProduct_Brand_CompSite_ComplaintCREATION_DATE
6
CC-00053479Bob SmithReview52Product 1DisneyNoSite8/24/20151
CC-00053530Bob SmithReview52Product 2GoofyNoSite8/24/20151
CC-00054168Bob SmithReview49Product 3MickeyNoSite422431
CC-00054507Bob SmithReview45Product 4DisneyNoSite8/31/20151
CC-00054909Bob SmithReview43Product 5GoofyNoSite422491
CC-00054959Bob SmithReview43Product 6MickeyNoSite422491
1 Solution

Accepted Solutions
Not applicable

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 #

View solution in original post

7 Replies
Anonymous
Not applicable

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?

crystles
Partner - Creator III
Partner - Creator III

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

Date(CreationDate) as Date

leocattqv
Creator
Creator
Author

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,

Anonymous
Not applicable

it's a good idea to define the format

but it should be like this:

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

Not applicable

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 #

MarcoWedel

maybe also possible:

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


or


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






leocattqv
Creator
Creator
Author

that worked, thank you