Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to enter the date while loading from a table?

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

1 Solution

Accepted Solutions
jsn
Employee
Employee

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.

View solution in original post

7 Replies
jsn
Employee
Employee

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.

somenathroy
Creator III
Creator III

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

Regards,

Som

anantmaxx
Specialist
Specialist

Hi ,

USE THIS

Load

ID,

Department,

Stage

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

From  Dummy.xlsx (ooxml, embedded labels);

VishalWaghole
Specialist II
Specialist II

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

nirav_bhimani
Partner - Creator III
Partner - Creator III

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

kaushiknsolanki

Hi,

     Try this.

    

     LOAD ID,

          Department,

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

     From  Dummy.xlsx (ooxml, embedded labels);

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Not applicable
Author

Thanks Som & Johannes...