Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

Hi Everyone,

i am trying to derive the date ,month and year fields from a date but the data in tat date field is incosnistent i am using the date# and date functions to manipulate the data but its not working .

syntax i am using :date(Date#(DateOfBusiness,'MM/DD/YYYY')) as Date,

Attached is the date i am using .please do help

kush141087gwassenaarrwunderlichmaxgrostalwar1cleveranjos

16 Replies
Highlighted
Specialist
Specialist

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

hi,

please use "alt" function??? and let me know

Date(Alt(Date#(DateOfBusiness,'MM/DD/YYYY'), Date#(DateOfBusiness,'YYYY-MM-DD')),'MM/DD/YYYY') as DateField



Highlighted
MVP
MVP

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

like this

timestamp(alt(Date,Timestamp#(Date,'YYYY-MM-DD hh:mm:ss')),'MM/DD/YYYY hh:mm:ss TT') as DateNew



Highlighted
MVP
MVP

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

Alt() function could be a way to go to cope with different timestamp formats in your input file.

In your specific setting, one of the two formats being the ISO format, it might be enough to use:

SET TimestampFormat = 'MM/DD/YYYY hh:mm:ss TT';

Data:

LOAD *,

    timestamp(Date) as DateNew2;

LOAD @1 as Date

FROM

[commdate.xls]

(biff, no labels, table is Sheet1$);

2016-06-19 12_37_37-QlikView x64 - [C__Users_Stefan_Downloads_test (38).qvw].png

Automatic Number Interpretation

Highlighted
Contributor III
Contributor III

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

its forming nulls

Highlighted
Contributor III
Contributor III

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

its forming nulls in date

Highlighted
Employee
Employee

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

Please try this

LOAD

  tfield,

  Date(Floor(tfield)) as Date,

  Month(Floor(tfield)) as Month,

  Year(Floor(tfield)) as Year;

LOAD

  alt(

  TimeStamp#(@1,'YYYY-MM-DD hh:mm:ss'),

  TimeStamp(@1),

  ) as tfield

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1064829-231962/commdate.xls]

(biff, no labels, table is Sheet1$);

Highlighted
Contributor III
Contributor III

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

this is forming nulls in date please take a look at the app i attached ,this is some kind of urgent pls help

Highlighted
MVP
MVP

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

Starting from your excel, I got this, see the attachment

1.png

Highlighted
Contributor III
Contributor III

Re: DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

please do check the sample i attached to the clever anjos reply the date filed is forming nulls