topic Re: Date format from text in QlikView

Date format from text

Wed, 31 Oct 2012 18:02:04 GMT

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

Re: Date format from text

MayilVahanan — Wed, 31 Oct 2012 18:06:26 GMT

Try like this

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

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

Hope it helps

Re: Date format from text

CELAMBARASAN — Wed, 31 Oct 2012 18:35:09 GMT

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;

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

From ExcelDatasource;

Re: Date format from text

whiteline — Fri, 02 Nov 2012 08:48:47 GMT

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).