Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Truncate not possible in qlikview or how to do?

Ok i have a lot of dates from different tables loaded from SQL into QVD (they need to stay the default like in the SQL table for other reports).

Example from dates i currently have

  1. 40099                          a timestamp witout hours, minutes and seconds. only Day's
  2. 40099,435405093         a timestamp with hours, minutes and seconds
  3. 16/08/2012                  a date
  4. 16/08/2012 12:55:23     a date with hours, minutes and seconds

Now i want them all to be like the first Example (40099) and they need to be Truncated like in sql (The hours, minutes and seconds need to be gone forever). But Qlikview has no Truncate function.

How can i convert 1, 2 and 3 to 1 with no (hidden)hours, minutes and seconds annymore?

Thanks a lot!

1 Solution

Accepted Solutions

Dates and timestamps are internally stored as numbers, so it is just a matter of truncating the number to an integer. Then you will have what you want.

You can load your examples using the following formulae

  1. Floor(Timestamp) as Daynumber
  2. Floor(Timestamp) as Daynumber
  3. Floor(Date#(Date,'DD/MM/YYYY')) as Daynumber
  4. Floor(Date#(TimeStamp,'DD/MM/YYYY hh:mm:ss')) as Daynumber

And if you have a mixture, you can use the following:

Floor(Alt(

     Date#(x,'DD/MM/YYYY hh:mm:ss'),

     Date#(x,'DD/MM/YYYY'),

     Num#(x,'#',',','.')

))

Yes, you can use string functions, but in my experience you get other problems then: You sometimes lose the dual/numeric property; you have no test that the date has been correctly interpreted, etc. So I always avoid string functions for date/time fields.

HIC

View solution in original post

3 Replies

The Floor() function does exactly this.

HIC

Not applicable

Can you explain me exactly how?

I also found out that the left(Date, 5) as Date can help a lot.

Dates and timestamps are internally stored as numbers, so it is just a matter of truncating the number to an integer. Then you will have what you want.

You can load your examples using the following formulae

  1. Floor(Timestamp) as Daynumber
  2. Floor(Timestamp) as Daynumber
  3. Floor(Date#(Date,'DD/MM/YYYY')) as Daynumber
  4. Floor(Date#(TimeStamp,'DD/MM/YYYY hh:mm:ss')) as Daynumber

And if you have a mixture, you can use the following:

Floor(Alt(

     Date#(x,'DD/MM/YYYY hh:mm:ss'),

     Date#(x,'DD/MM/YYYY'),

     Num#(x,'#',',','.')

))

Yes, you can use string functions, but in my experience you get other problems then: You sometimes lose the dual/numeric property; you have no test that the date has been correctly interpreted, etc. So I always avoid string functions for date/time fields.

HIC

View solution in original post