Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
MVP
MVP

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

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


Highlighted
Employee
Employee

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

Well, my scripts runs with your excel file

Highlighted
Contributor III
Contributor III

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

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

Highlighted
Employee
Employee

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

You didn´t use my solution into your qvw.

Could you please give a try?

Highlighted
Contributor III
Contributor III

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

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

Highlighted
Master
Master

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

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

Highlighted
Employee
Employee

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

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,