topic Date Formatting in QlikView

Date Formatting

Tue, 06 Sep 2011 13:06:21 GMT

Hi All,

I am using Qlikview10.

I have a Date field in my table but it is in string format.

I have to arrange the report By Week. I have an excel sheet where year, week, fdate, ldate fields are there which indicate that which date falls on which week of which year.

But I can't able to understand how to implement this to my report/table.

Please Suggest.

Date Formatting

its_anandrjs — Tue, 06 Sep 2011 13:17:20 GMT

Hi,

For this i suggest load date with proper date format like

Date( Date#( DateField, 'MM/DD/YYYY' ) ) as Date

or convert it with any other date commands like

Year, Week etc

Regards,

Anand

Date Formatting

its_anandrjs — Tue, 06 Sep 2011 13:24:48 GMT

Hi,

I also suggest if in the excel it is into string format then use the Makedate function to make dates like

Makedate( Left( DateField , 4 ), Mid( DateField , 5 , 2 ),Right( DateField , 2 ) ) as Date

to create a date field

use above for year, week

Year(Makedate( Left( DateField , 4 ), Mid( DateField , 5 , 2 ),Right( DateField , 2 ) ) ) as Year

Regards,

Anand

Date Formatting

Tue, 06 Sep 2011 13:34:00 GMT

Using Anand's date functions above it then sounds like you want to create a 'time dimensions' temp table from your excel file and use this so you can bring in the Week etc to your main table?

You'll want to use the date function on your table date and your excel date in the same way so that you can join on that and pull in Week etc to your main table

hope that makes sense

Joe

Re: Date Formatting

Tue, 06 Sep 2011 13:43:44 GMT

Please explain in Brief.. I am not very familiar with the Dates..

When I am converting "20110626" string to date it is giving me "56961-01-07".

How can I convert "20110626" string to year,month,week,day?

Thx.

Date Formatting

its_anandrjs — Tue, 06 Sep 2011 13:51:30 GMT

Hi,

Use this script for make

Year

Year(Makedate( Left( DateField , 4 ), Mid( DateField , 5 , 2 ),Right( DateField , 2 ) ) ) as Year

Month

Month(Makedate( Left( DateField , 4 ), Mid( DateField , 5 , 2 ),Right( DateField , 2 ) ) ) as Month

Week

Week(Makedate( Left( DateField , 4 ), Mid( DateField , 5 , 2 ),Right( DateField , 2 ) ) ) as Week

Day

Day(Makedate( Left( DateField , 4 ), Mid( DateField , 5 , 2 ),Right( DateField , 2 ) ) ) as Day

Regards,

Anand

Re: Date Formatting

its_anandrjs — Tue, 06 Sep 2011 13:58:24 GMT

Hi,

See the sample file it works for you.

Regards

Anand

Re: Date Formatting

Tue, 06 Sep 2011 14:33:50 GMT

Hi Anand,

Please Find Attached of the qvd file.

By seeing it you will get to know when I am converting into date format it is not giving me the same dates.. Why??

Re: Date Formatting

its_anandrjs — Tue, 06 Sep 2011 14:50:52 GMT

Hi,

It seems okay because there is only two months data see the Sheet1 in attached sample file

Reagrds

Anand

Re: Date Formatting

Tue, 06 Sep 2011 14:58:49 GMT

Your 'Date' field and your 'InvoiceDate' field are in seperate unjoined tables so the table you have done there will give you all dates for both fields, Anand's code works correctly you just need to join the fields

thanks

Joe

edit: make your load

Tab1:

Load

@1 As CustDate,

@2 As InvoiceNo,

@3 As InvoiceDate,

Makedate( Left( @3, 4 ) , Mid( @3, 5 , 2), Right( @3 , 2)) As Date

From

IMS/Headers/Type1New.txt

Re: Date Formatting

its_anandrjs — Tue, 06 Sep 2011 15:21:09 GMT

Hi,

I am not able to reload your table but its okay yes as Simmons say you need to join the fields or use @3 as a field to make date use this

Load

Makedate( Left( @3, 4 ), Mid( @3, 5 , 2 ),Right( @3, 2 ) ) as Date

Resident Tab1;

Regards,

Anand

Re: Date Formatting

Tue, 06 Sep 2011 17:38:33 GMT

Perfect.

Thanks Simmons and Anand.

Your work is really appreciable.

Date Formatting

Tue, 14 Feb 2012 18:37:53 GMT

Anand - do you have any suggestions on how to format a date that looks like this 11912 to be 1/19/2012?

Date Formatting

Anonymous — Wed, 15 Feb 2012 07:08:44 GMT

Hi,

Use the following example of Load statement

LOAD

Date,

Year(Date) AS Year,

Month(Date) AS Month,

Day(Date) AS Day

LOAD

Date(Date#(DateField, 'YYYYMMDD')) AS Date

FROM ABC.txt;

In the above script you can arrive the Year, Month, Day and Date in valid format. By using Month, Year and Day dimensions you can create the charts.

Date Formatting

jagan — Wed, 15 Feb 2012 07:54:43 GMT

Hi,

Use Date#()

=Date(Date#(DateFieldName, 'MDDYY'), 'M/DD/YYYY')

Hope this helps you.

Regards,

Jagan.