Date Formatting

[#1]

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.

[its_anandrjs]

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

[its_anandrjs]

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

[#4]

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

[#5]

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.

[its_anandrjs]

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

[its_anandrjs]

Hi,

See the sample file it works for you.

Regards

Anand

[#8]

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??

[its_anandrjs]

Hi,

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

Reagrds

Anand

[#10 · Accepted solution]

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