topic Re: Date format from csv in QlikView

Date format from csv

Anonymous — Wed, 08 May 2013 15:50:51 GMT

Hello,

I have csv file with format date like 1/2/2012 15:27

Qlikview recognises it like text.

I used load script

date(date#(created_at,'YYYY-MM-DD')) as creationdate1,

It didn't work.

Who knows what should to do?

Thanks,

Re: Date format from csv

Nicole-Smith — Wed, 08 May 2013 16:05:03 GMT

date(timestamp#(created_at, 'D/M/YYYY hh:mm'), 'YYYY-MM-DD')

The timestamp#() tells QV how the date is formatted in the .csv.

The date() tells QV how you want it formatted.

Re: Date format from csv

shree909 — Wed, 08 May 2013 16:06:20 GMT

Hi use this

You need to specify the same date format that has in csv file..

date(date#(created_at,'M/D/YYYY'),'MM/DD/YYYY')

Re: Date format from csv

Anonymous — Thu, 09 May 2013 19:10:18 GMT

Hello again,

I need to calculate week.

I tried to use as you suggested

dual(WeekStart(date(timestamp#(created_at, 'D/M/YYYY hh:mm'), 'YYYY-MM-DD'))& '-' &WeekEnd(date(timestamp#(created_at, 'D/M/YYYY hh:mm'), 'YYYY-MM-DD')),

WeekStart(date(timestamp#(created_at, 'D/M/YYYY hh:mm'), 'YYYY-MM-DD')))as WEEK

but it doesn't recognize like date

What's wrong this dual function?

Thanks

Re: Date format from csv

Anonymous — Fri, 10 May 2013 15:10:53 GMT

Any ideas?

Re: Date format from csv

rustyfishbones — Fri, 10 May 2013 15:28:18 GMT

Whwn you say you need to calculate Week is it a date field or a numeric field

Do you want to calculate Sales this week or just add the week number using the date

to add the WeekNumber try,

if you want to calculate Sales this week it would be like this

Re: Date format from csv

Anonymous — Fri, 10 May 2013 16:03:23 GMT

In csv file: my field created_at looks like 04/09/2011 6:47:00 PM. When I open it in excel, format cell is custom.

Excel thinks it text field, because it suggests to sort from A or Z.

I need to see first day and last day of the week and also I need sort it by asc or desc.

Thanks,

Re: Date format from csv

Anonymous — Fri, 10 May 2013 16:11:54 GMT

Sorry, but it didn't work. It recodes only untill 4/12/2013 -> 2013-12-04.

I don't know why?

Re: Date format from csv

Anonymous — Fri, 10 May 2013 16:14:41 GMT

Maybe I need to change somewhere settings for date format?

Re: Date format from csv

rustyfishbones — Fri, 10 May 2013 16:30:51 GMT

Try this video I made on YouTube that may help

http://youtu.be/Xchd-X4FwC4

Re: Date format from csv

Anonymous — Tue, 14 May 2013 20:43:22 GMT

I downloaded a new file and the date format shows like

2011-05-16 10:33:58 -0400

2011-05-16 12:29:31 -0400

2011-05-16 14:25:00 -0400

2011-05-16 19:30:45 -0400

2011-05-16 20:42:34 -0400

2011-05-17 18:56:56 -0400

2011-05-18 11:22:20 -0400

2011-05-18 16:28:25 -0400

2011-05-19 13:12:39 -0400

How to remove UTC time?

I use

date(timestamp#(created_at, 'M/D/YYYY hh:mm:ss'), 'YYYY-MM-DD') as creationdate,

What's the code for UTC?

Thanks,

Re: Date format from csv

Anonymous — Wed, 15 May 2013 01:55:47 GMT

???

Re: Date format from csv

rustyfishbones — Wed, 15 May 2013 07:57:58 GMT

try

DATE(created_at) AS created_at