Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Having problem loading data as DATE format

I have tried:

1) Date([Begin Date]) as EntryDate and

2) Date(Date#([Begin Date], 'YYYY-MM-DD')) as EntryDate

but keep getting "null" or "-" as the result. Attached is the original excel data.

Many thanks.

1 Solution

Accepted Solutions
jsn
Honored Contributor

Having problem loading data as DATE format

Your problem here is the "GMT" in the timestamp value. When using Date# to interpret a value you need to capture and parse the entire string like this: date#(DATE, 'YYYY-MM-DD hh:mm GMT'). We can't however write it like this since M in the interpretation represents Month and there is unfortunately no kind of escape character. If the timestamps all look the same and you want to extract just the date you could just strip the first part of the timestamp values out and format this as a date:

date(date#(left([Begin Date],10),'YYYY-MM-DD'),'Your Choice of Format') as EntryDate

4 Replies
vgutkovsky
Honored Contributor II

Having problem loading data as DATE format

Looks like your excel data is formatted as a timestamp. If there is a way you can change the formatting, I would recommend that you do that. Otherwise, try using the timestamp# transformation and then use makedate with year(field), month(field), and day(field).

Regards,

MVP
MVP

Having problem loading data as DATE format

Maybe something like this:

date(daystart(timestamp#("Your Field",'Your Input Timestamp Format')),'Your Desired Date Format') as EntryDate

jsn
Honored Contributor

Having problem loading data as DATE format

Your problem here is the "GMT" in the timestamp value. When using Date# to interpret a value you need to capture and parse the entire string like this: date#(DATE, 'YYYY-MM-DD hh:mm GMT'). We can't however write it like this since M in the interpretation represents Month and there is unfortunately no kind of escape character. If the timestamps all look the same and you want to extract just the date you could just strip the first part of the timestamp values out and format this as a date:

date(date#(left([Begin Date],10),'YYYY-MM-DD'),'Your Choice of Format') as EntryDate

Not applicable

Having problem loading data as DATE format

Thank you. That did the trick.