topic Re: Load date based on predefined Times in QlikView

Load date based on predefined Times

anuradhaa — Thu, 05 Nov 2015 09:13:36 GMT

Hi,

My script loads every one hour. here i want to load the data with the check on event time and reload time.

ex - I have three predefined hours

06:30 , 14:30 and 22:30

if my script loads 10:00 AM i want to load all events happen before 06:30 AM

if my script loads 06:00 PM i want to load all events happen before 02:30 PM

if my script loads 11:00 PM i want to load all events happen before 10:30 PM

I take hours as cut off limits.

Please help me to do this

Re: Load date based on predefined Times

Gysbert_Wassenaar — Thu, 05 Nov 2015 10:48:37 GMT

Maybe something like this:

LET vTime = num(pick(match(Hour(Now()),'10','18','23'), MakeTime(6,30), MakeTime(14,30), MakeTime(22,30)));

MyData:

LOAD *

FROM MySource

WHERE MyTime < $(vTime);

Re: Load date based on predefined Times

anuradhaa — Thu, 05 Nov 2015 11:03:41 GMT

Thanks.

But here script load times may very and i want to check what is the closed time to script load time.

Then select that close time from 06.30,14.30,22.30 and load date grater than that time

Re: Load date based on predefined Times

Gysbert_Wassenaar — Thu, 05 Nov 2015 11:15:31 GMT

LET vTime = num(If(Hour(Now())<10,MakeTime(6,30),If(Hour(Now())<18,MakeTime(14,30),If(Hour(Now())<23,MakeTime(22,30)))));

Re: Load date based on predefined Times

anuradhaa — Thu, 05 Nov 2015 11:34:21 GMT

when loading script it says

Field not found - <<>

[HRM]:

LOAD

MOV_ID as hmp_MOV_ID,

hmp_num,

HMP_time

RESIDENT HISTORY where HMP <

Re: Load date based on predefined Times

Gysbert_Wassenaar — Thu, 05 Nov 2015 11:38:51 GMT

Yeah, there were some typos in my last post. Try again with this one:

LET vTime = num(If(Hour(Now())<10,MakeTime(6,30),If(Hour(Now())<18,MakeTime(14,30),If(Hour(Now())<23,MakeTime(22,30)))));

Re: Load date based on predefined Times

anuradhaa — Thu, 05 Nov 2015 13:11:21 GMT

another one more thing ,

if i want to categorize basd on shift times how can i do that,

I have tried

if(mydate<= $(vTime) - Hour(8) , 'S1',if(mydate> $(vTime) -Hour(8) and mydate <= $(vTime) - Hour(16) , 'S2',if(mydate > $(vTime) - Hour(16) and mydate<= $(vTime) - Hour(24) , 'S3'))) as shifts

in script but not work

Re: Load date based on predefined Times

Gysbert_Wassenaar — Thu, 05 Nov 2015 13:32:42 GMT

Maybe like this: if(frac(mydate)< 1/3, 'S1', if(frac(mydate)<2/3,'S2', 'S3')) as shift. 8 hours is one third of a day, 8 divided by 24 or 1/3.

Re: Load date based on predefined Times

anuradhaa — Thu, 05 Nov 2015 13:53:39 GMT

But my shift times are different 06:30 , 14:30 and 22:30

Re: Load date based on predefined Times

Gysbert_Wassenaar — Thu, 05 Nov 2015 13:57:24 GMT

Then you'll need to adapt the expression so it fits your needs. If you know how to perform divisions then this shouldn't be any problem. what is 6.5 divided by 24?

Re: Load date based on predefined Times

anuradhaa — Thu, 05 Nov 2015 14:16:16 GMT

It's not that accurate,

Please find attached for times.

I need to put them to latest 3 shifts,

06:30 - 14:30

14:30 - 22:30

22:30 - 06.30 next day