Grouping times & days in the load script for listbox filtering

[#1]

My data has 24hr time in one cell and the actual day in another cell

eg.  Hr =18:00  ;  Day of Week = Sat

What I need to do is create listbox groups for the following:

'Business Hrs'  =  Mon - Fri 8am - 6pm , Sat 8am-12pm

'AH-Sociable' = Mon - Fri 6pm - 11pm

'AH-Unsociable' = Mon - Fri 11pm - 8am , Sat 12pm - 8am , Sun All day

so my audience can choose either Business Hrs/AH-Sociable/AH-Unsociable to see the number of attendees arriving in these hrs.

I was trying to create these groups in the load script, but not having an IT background I'm struggling. Any help with the load script would be appreciated.

Thanks.

[sunny_talwar]

Would you be able to share some raw data to create the new filter?

[Anonymous · Accepted solution]

I created a quick demo for this. Attached you'll find qvf and 2 demo csv files, below there is a script example.

-------------------------------------------------------------

[Filter]:

LOAD

[Filter],

  [Day],

  [StartHour],

  [EndHour]

FROM [lib://QlikSense/Filter.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[Data]:

LOAD

[DataDay],

  [DataHour],

  [Quantity]

FROM [lib://QlikSense/Data.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

LEFT JOIN (Data)

LOAD

Day AS DataDay,

StartHour AS DataStartHour,

EndHour AS DataEndHour,

Filter AS DataFilter

RESIDENT Filter;

Data_Filter_Temp:

LOAD

DataDay,

DataHour,

Quantity,

DataStartHour,

DataEndHour,

DataFilter,

IF(Replace(DataHour,':','')>DataStartHour*100 And Replace(DataHour,':','')<=DataEndHour*100,1,0) As FilterFlag

RESIDENT Data;

Drop Table Data;

Data_Filter:

LOAD

DataDay AS Filter_DataDay,

DataHour AS Filter_DataHour,

Quantity AS Filter_Quantity,

DataStartHour AS Filter_DataStartHour,

DataEndHour AS Filter_DataEndHour,

DataFilter AS Filter_DataFilter,

FilterFlag AS Filter_FilterFlag

RESIDENT Data_Filter_Temp WHERE

FilterFlag=1;

Drop Table Data_Filter_Temp;

-------------------------------------------------------------

Clea, could you check if this is what you're looking for.

BW,

Markus

[#4]

Thanks Markus - It took me awhile to work out what you have done, but now I understand it, it makes sense. Thank you for taking the time to do this.