DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

[Anonymous]

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

kush141087‌gwassenaar‌rwunderlich‌maxgro‌stalwar1‌cleveranjos‌

[Anonymous]

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

[Kushal_Chawda]

like this

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

[swuehl]

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

Automatic Number Interpretation

[Anonymous]

its forming nulls

[Anonymous]

its forming nulls in date

[Clever_Anjos]

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

[Anonymous]

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

[maxgro]

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

[Anonymous]

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