topic Re: only keep date for time stamp in script in QlikView

only keep date for time stamp in script

yelin_nyu — Fri, 26 Oct 2012 22:06:37 GMT

Hi,

I know in expression, you can use date(), but what about in script? Is there an easy way to only keep the date, because it's giving me problems when i manipulate data from script (duplicate dates because hours and mins don't match)

Thanks

Re: only keep date for time stamp in script

swuehl — Fri, 26 Oct 2012 22:24:05 GMT

Use daystart() or floor() to get rid of the time information (I assume your timestamps are correctly interpreted as such by QV):

Date(Floor(TIMESTAMP)) as DATE

Re: only keep date for time stamp in script

yelin_nyu — Fri, 26 Oct 2012 22:33:53 GMT

date() function won't work in script because date' is not a recognized built-in function name. Also, I tried daystart(), I didn't work either

Re: only keep date for time stamp in script

MayilVahanan — Fri, 26 Oct 2012 22:37:08 GMT

i think try with date#() function..may be format issuse..

Re: only keep date for time stamp in script

yelin_nyu — Fri, 26 Oct 2012 22:40:59 GMT

no, date#' is not a recognized built-in function name. Sorry guys, this is in script, not on front end. it's part of sql code (sql select)

Re: only keep date for time stamp in script

swuehl — Fri, 26 Oct 2012 22:44:25 GMT

Ah, I missed that part of your problem.

Use a preceding LOAD statement:

LOAD

Date(floor(TIMESTAMP)) as DATE;

SQL SELECT ...

Re: only keep date for time stamp in script

yelin_nyu — Fri, 26 Oct 2012 23:45:40 GMT

this is what i used:

Order:

Load *

case when source='f' and rcvd_date>load_date-3

then Date(floor(load_date)+7)

else Date(floor(load_date))

end as LD

resident tempOrder;

error msg missing FROM statement (I know it's this part becuz when i commented out everything, and try load a little by little, the code worked until I uncommented the above case statement.

Re: only keep date for time stamp in script

MayilVahanan — Fri, 26 Oct 2012 23:59:08 GMT

try like this

load *, if(source = 'F' and rcd_date>loaddate-3,date(floor(load_date)+7),date(load_date)) as ld resident tablename;

Hope it helps