Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date format from text

I loaded a excel file which has a date field but when I tried to pull Day(FIELD),Month(FIELD) I am getting nothing, That field contains data for One month but still I am getting nothing for Day(Field)....please help me

1 Solution

Accepted Solutions
celambarasan
Not applicable

Re: Date format from text

Hi,

You have to format to date if it is not in correct format

Assuming my date field is in the format MM-DD-YYYY then

Check with this

DateFormatting:

Load Day(FieldName) AS day,Month(FieldName) AS Month;

Load Date#(DateField,'MM-DD-YYYY') AS FieldName From ExcelDatasource;

Or

Load Day(Date#(DateField,'MM-DD-YYYY')) AS day,Month(Date#(DateField,'MM-DD-YYYY')) AS Month

From ExcelDatasource;

3 Replies
mayilvahanan
Not applicable

Re: Date format from text

HI

Try like this

Load *,day(Date) as Day, Month(Date) as Month;

Load *,Date#(DateField) as Date from excelfilename.xls;

Hope it helps

celambarasan
Not applicable

Re: Date format from text

Hi,

You have to format to date if it is not in correct format

Assuming my date field is in the format MM-DD-YYYY then

Check with this

DateFormatting:

Load Day(FieldName) AS day,Month(FieldName) AS Month;

Load Date#(DateField,'MM-DD-YYYY') AS FieldName From ExcelDatasource;

Or

Load Day(Date#(DateField,'MM-DD-YYYY')) AS day,Month(Date#(DateField,'MM-DD-YYYY')) AS Month

From ExcelDatasource;

whiteline
Not applicable

Re: Date format from text

Hi.

To be able to use Date functions you should store your date in field in a right format.

Actually dates has two parts: text and numeric. It seems that you have just string in FIELD so date functions doesn't work.

So you should convert it to make a date from string. There are functions with # in the end that converts string into dual.

Above you can see the example of conversion in script.

You can also try it in a chart/listbox to make sure that the statement is correct (if you have loaded FIELD separately).