Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qvhelpplease
New Contributor II

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
galax_allu
Valued Contributor

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



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



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

qvhelpplease
New Contributor II

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

its forming nulls

qvhelpplease
New Contributor II

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

its forming nulls in date

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$);

qvhelpplease
New Contributor II

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

MVP
MVP

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

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

1.png

qvhelpplease
New Contributor II

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

Community Browser