Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NellyAcko
Contributor III
Contributor III

Convert Time Expression to Load Script equivalent

I have this expression which works as it should.

=if(MakeTime([Hour Slot]) >= Open and MakeTime([Hour Slot]) <= Close ,1,0)

Open = Time an outlet opens EG 10:00

Close = Time an outlet closes EG 17:00

Hour slot ranges 1 to 24 for all the hours in the day. 

I want only load the data for the trading hours between open and close not all 24 hour periods. 

[Hour Slot] is in a different table to Open and Close

4 Replies
MayilVahanan

HI @NellyAcko 

Try like this

Load * where Flag = 1;

Load *, if(MakeTime([Hour Slot]) >= Open and MakeTime([Hour Slot]) <= Close ,1,0) as Flag from ursource;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
NellyAcko
Contributor III
Contributor III
Author

Hi I had tried very similar approach to the above but i get Field 'Open' not found.

I am loading this field as part of a crosstab table load code below.

Table output is:

Outlet ID, Day, Open, Close

FC001, Monday, 08:00, 18:00

FC001, Tuesday, 08:00, 18:00

...

[Temp_Open_Time]:
Crosstable ("Day", "Open", 1) Load
"Outlet ID",
"Monday open" as "Monday",
"Tuesday open" as "Tuesday",
"Wednesday open" as "Wednesday",
"Thursday open" as "Thursday",
"Friday open" as "Friday",
"Saturday open" as "Saturday",
"Sunday open" as "Sunday"
FROM [lib://Trading User 0016507]]
(ooxml, embedded labels, header is 2 lines, table is [Open Outlets]);

[Temp_Close_Time]:
Crosstable ("Day", "Close", 1) Load
"Outlet ID",
"Monday close" as Monday,
"Tuesday close" as Tuesday,
"Wednesday close" as Wednesday,
"Thursday close" as Thursday,
"Friday close" as Friday,
"Saturday close" as Saturday,
"Sunday close" as Sunday
FROM [lib://Trading User 0016507]
(ooxml, embedded labels, header is 2 lines, table is [Open Outlets]);

Noconcatenate
[Opening Times]:
Load
*
Resident [Temp_Open_Time];
join
Load
*
Resident [Temp_Close_Time];

MayilVahanan

HI,

Can you send the sample file to verify

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
NellyAcko
Contributor III
Contributor III
Author

Hi Mayil, here are examples of the data files.

Many Thanks