topic Re: use MakeTime and MakeDate together in QlikView

use MakeTime and MakeDate together

anuradhaa — Tue, 10 Nov 2015 06:09:23 GMT

Hi,

I need to load date which is less than specific date. here in date it has to be in 'YYYY-MM-DD hh:mm' format.

Here date is reload date and hh:mm is MakeTime(6,30).

MakeTime value is dynamic.

so how can i get

mydate < reloaddate in 'YYYY-MM-DD' and Maketime(6,30)

Re: use MakeTime and MakeDate together

Anonymous — Tue, 10 Nov 2015 06:11:36 GMT

Do you want to create this MYDate field or you already have this?

Re: use MakeTime and MakeDate together

anuradhaa — Tue, 10 Nov 2015 06:13:53 GMT

I already have that

Re: use MakeTime and MakeDate together

Anonymous — Tue, 10 Nov 2015 06:19:36 GMT

will this work?

like:

load

date(MyDateField,'YYYY-MM-DD') as MyDateField

From tablename where num(MyDateField)<=num(reloaddate)

Re: use MakeTime and MakeDate together

Anonymous — Tue, 10 Nov 2015 06:21:20 GMT

Making time from your MyDateField seems tough..

For that I guess you need to go for looping to make time field...

Re: use MakeTime and MakeDate together

Kushal_Chawda — Tue, 10 Nov 2015 06:22:35 GMT

what is the format of Your date?

Re: use MakeTime and MakeDate together

jyothish8807 — Tue, 10 Nov 2015 06:37:12 GMT

Hi Anuradha,

Try like this:

load

date(Your field,'YYYY-MM-DD h:mm:ss[.fff] TT')) as Yourfield,

reloaddate

from <>

where date(Your field,'YYYY-MM-DD h:mm:ss[.fff] TT')) < reloaddate;

Hope it helps

Regards

Re: use MakeTime and MakeDate together

anuradhaa — Tue, 10 Nov 2015 06:44:28 GMT

YYYY-MM-DD hh:mi:ss

Re: use MakeTime and MakeDate together

anuradhaa — Tue, 10 Nov 2015 06:47:52 GMT

No, I want to dinamicaly calculate that date i want to use in where clause.

Get Date from reload date and time from my expression.

I know how to calculate that time.

But i don't know how to join that date and time inside where clause.

Say my time is 06:30 and date is 2015-11-09

i want to use

myDate < 2015-11-09 06:30 in where clause

Re: use MakeTime and MakeDate together

jyothish8807 — Tue, 10 Nov 2015 06:48:13 GMT

And yes, if reload time is combination of two fields then please combine them and make one field and use in script.

Load

date(Your field,'YYYY-MM-DD h:mm:ss[.fff] TT')) as Yourfield,

date(ReloadTime() ,'YYYY-MM-DD') &' '& MakeTime(6,3) as reloaddate

where date(Your field,'YYYY-MM-DD h:mm:ss[.fff] TT')) <date(ReloadTime() ,'YYYY-MM-DD') &' '& MakeTime(6,3)

Regards

Re: use MakeTime and MakeDate together

Anonymous — Tue, 10 Nov 2015 06:53:07 GMT

Try to compare into numeric like:

where num(date(Your field,'YYYY-MM-DD hh:mm:ss[.fff] TT'))) <num(date(ReloadTime() ,'YYYY-MM-DD') &' '& time(MakeTime(6,3),'hh:mm'))

Hope this will help!!

Re: use MakeTime and MakeDate together

anuradhaa — Tue, 10 Nov 2015 07:35:23 GMT

hi I have used "num(date(ReloadTime() ,'YYYY-MM-DD') &' '& time(MakeTime(6,3),'hh:mm'))" in text object and it shows "-"

Re: use MakeTime and MakeDate together

Anonymous — Tue, 10 Nov 2015 07:40:34 GMT

Remove num() as its not working, need to see into it..

You can do like:

=date(ReloadTime(),'DD/MMM/YYYY')&' '&Time(MakeTime(6,30),'hh:mm')

Re: use MakeTime and MakeDate together

Kushal_Chawda — Tue, 10 Nov 2015 07:44:59 GMT

try,

where

timestamp(YourDate,'YYYY-MM-DD hh:mm')<timestamp(date(floor(ReloadTime()) ,'YYYY-MM-DD') + time(MakeTime(6,30),'hh:mm'),'YYYY-MM-DD hh:mm')

Re: use MakeTime and MakeDate together

jyothish8807 — Tue, 10 Nov 2015 08:02:54 GMT

have you tried my solution?

Regards

Re: use MakeTime and MakeDate together

MarcoWedel — Sun, 15 Nov 2015 00:45:13 GMT

Hi,

one solution could be also:

myDate < Floor(ReloadTime())+'06:30'

hope this helps

regards

Marco