Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

date and Date# function

Hi Friends,

I am getting error while working on date(date#,format) function , any body could give me the help on this please.

14 Replies
Highlighted

What is the expression you are using?

Highlighted
Not applicable

I have taken date field as expression.

I am using it at script level like

date(date#(datefield,'M/D/YY'),'YYYY/MM/DD')

Highlighted

What is the error that you get when you use this? I don't see anything wrong with the syntax

Highlighted
Not applicable

Hi Sunny,

data not loading into that field

Highlighted

Can you show how datefield look in its raw form?

Highlighted
Not applicable

Hi Sunny,

This  is my data

LOAD DEPARTMENT_ID,

     EMPLOYEE_ID,

     END_DATE,

     JOB_ID,

     date(date#(START_DATE,'yyyy/dd/mm'),'yy/d/m/') as daa

FROM

E:\ALL_HR_8_TABLES_DETAILS_VIEW.xls

(biff, embedded labels, table is [JOB_HISTORY$]);

Highlighted

Two things...

1) why do you have an extra / at the end here? Typo?

Capture.PNG

2) Month is MM (upper case m) and Minute is mm (lower case m)

Try this

LOAD DEPARTMENT_ID,

     EMPLOYEE_ID,

     END_DATE,

     JOB_ID,

     date(date#(START_DATE,'YYYY/DD/MM'),'YY/D/M') as daa

FROM

E:\ALL_HR_8_TABLES_DETAILS_VIEW.xls

(biff, embedded labels, table is [JOB_HISTORY$]);

Assuming START_DATE is like this in your Excel file -> 2017/05/01

Highlighted
Not applicable

Hi sunny,

Thanks for quick response.

My date format is like 'M/D/YYYY'

Highlighted

And you need it to be in YY/D/M format?

LOAD DEPARTMENT_ID,

    EMPLOYEE_ID,

    END_DATE,

    JOB_ID,

    date(date#(START_DATE,'M/D/YYYY'),'YY/D/M') as daa

FROM

E:\ALL_HR_8_TABLES_DETAILS_VIEW.xls

(biff, embedded labels, table is [JOB_HISTORY$]);

M/D/YYYY - your current format

YY/D/M - your needed format

Read more here:

Why don’t my dates work?

QlikView Date fields

Get the Dates Right