topic How to enter the date while loading from a table? in QlikView

How to enter the date while loading from a table?

Fri, 22 Mar 2013 06:56:25 GMT

Hi All,

I am extracting the data from an Excel sheet that does not contain the date.

But that date needs to store in the QVD as a column along with the excel sheet data.

My data is as follows...

ID Department Stage Month_Date

----------------------------------------------------------------------------------------------

123 Abc Stage1 31/01/2013

111 Xyz Stage 2 31/01/2013

I extracte the data as follows

LOAD ID,

Department,

Stage,
31/01/2013 as Month_Date

From Dummy.xlsx (ooxml, embedded labels);

But QV is taking the Month_Date field as Number, after changing that number to Date format using "Override Document Settings" in the properties of the list box, It is showing that

Re: How to enter the date while loading from a table?

Anonymous — Fri, 22 Mar 2013 07:00:24 GMT

To be on the safe side you can do:

LOAD ID,

Department,

Stage,
date(date#('31/01/2013', 'DD/MM/YYYY'),'DD/MM/YYYY') as Month_Date

From Dummy.xlsx (ooxml, embedded labels);

If you just put 31/01/2013 I think it will be evaluated as 31 divided by 1 divided by 2013.

Re: How to enter the date while loading from a table?

somenathroy — Fri, 22 Mar 2013 07:00:47 GMT

Use Date(Date#('31/01/2013'.'DD/MM/YYYY'),'DD/MM/YYYY') as as Month_Date in LOAD statement.

Regards,

Som

Re: How to enter the date while loading from a table?

Anonymous — Fri, 22 Mar 2013 07:04:09 GMT

Hi ,

USE THIS

Load

ID,

Department,

Stage

Date(today(),'DD/MM/YYYY') as Date

From Dummy.xlsx (ooxml, embedded labels);

Re: How to enter the date while loading from a table?

VishalWaghole — Fri, 22 Mar 2013 07:05:08 GMT

Please use below script :-

LOAD ID,

Department,

Stage,
date(Month_Date,'DD-MM-YYYY') as Month_Date

From Dummy.xlsx (ooxml, embedded labels);

Thanks,

Vishal

Re: How to enter the date while loading from a table?

nirav_bhimani — Fri, 22 Mar 2013 07:06:22 GMT

Hi,

You want 01 as month then try this function

Month(DATE#('31/01/2013' , 'DD/MM/YYYY') )

Hope it will resolve ur problem.

Regards,

Nirav Bhimani

Re: How to enter the date while loading from a table?

kaushiknsolanki — Fri, 22 Mar 2013 07:08:54 GMT

Hi,

Try this.

LOAD ID,

Department,

Stage,
Date(Makedate(2013,01,31)) as Month_Date

From Dummy.xlsx (ooxml, embedded labels);

Regards,

Kaushik Solanki

Re: How to enter the date while loading from a table?

Fri, 22 Mar 2013 07:19:03 GMT

Thanks Som & Johannes...