3 Replies Latest reply: Jul 28, 2016 7:16 PM by Clea Myers RSS

    Grouping times & days in the load script for listbox filtering

    Clea Myers

      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.

        • Re: Grouping times & days in the load script for listbox filtering
          Sunny Talwar

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

          • Re: Grouping times & days in the load script for listbox filtering
            Markus Määttälä

            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