Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
maxgro
MVP
MVP

If I add in your .qvw a listbox with

Timestamp( Alt(Timestamp(Timestamp#(DateOfBusiness, 'YYYY-MM-DD hh:mm:ss')), DateOfBusiness) )

it seems to work,

1.png

so try to replace in your script

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

with

Timestamp( Alt(Timestamp(Timestamp#(DateOfBusiness, 'YYYY-MM-DD hh:mm:ss')), DateOfBusiness) ) as DateNew


Clever_Anjos
Employee
Employee

Well, my scripts runs with your excel file

Anonymous
Not applicable
Author

but at the last of the list box u can find nulls or just take a year or month list box where u can find nulls

Clever_Anjos
Employee
Employee

You didn´t use my solution into your qvw.

Could you please give a try?

Anonymous
Not applicable
Author

i have used your solution but there are some numbers and other data in fields of month and year

attached is the qvw for reference

effinty2112
Master
Master

Hello,

Try

LOAD

Month(Date) as Month,

Year(Date) as Year,

Date

;

LOAD

Date(Alt(

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

Floor(Timestamp#(@1,'DD/MM/YYYY hh:mm:ss'))

))

as Date

FROM

commdate.xls

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

Cheers

Clever_Anjos
Employee
Employee

Please slightly change your code to this, have to check this DateOfBusiness field.

LOAD 

  tfield, 

  Date(Floor(tfield)) as Date, 

  Month(Floor(tfield)) as Month, 

  Year(Floor(tfield)) as Year,

  DateOfBusiness; 

LOAD UniqueID,

      DateOfBusiness,

      Timestamp( Alt(Timestamp(Timestamp#(DateOfBusiness, 'YYYY-MM-DD hh:mm:ss')), DateOfBusiness) ) as DateNew,

      alt( 

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

  TimeStamp(DateOfBusiness), 

  ) as tfield  ,

     FKStoreID as StoreId,

     FKEmployeeNumber,