Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
NellyAcko
Contributor II
Contributor II

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
NellyAcko
Contributor II
Contributor II
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
NellyAcko
Contributor II
Contributor II
Author

Hi Mayil, here are examples of the data files.

Many Thanks