5 Replies Latest reply: Jan 11, 2016 12:40 AM by Jo Tedesco RSS

    Possible in QlikView?  Polling activity in 10 minute blocks

    Jo Tedesco

      this is what i can produce

       

       

      It shows when an activity is scheduled to start and the duration of the activity.

      However what I would really like is that the chart to show what is happening every 5 or 10  minutes.

       

      so for example red activity starts at 12:40 and is scheduled to go for 15 minutes

      I would like it to show something like:

      12:40     red activity

      12:45      red activity

      12:50     red activity

      12:55         red activity

      13:00    

      13:05     green activity

      13:10     green activity

      13:15     green activity

       

      Thank you

      Jo

        • Re: Possible in QlikView?  Polling activity in 10 minute blocks
          Martin Mahler

          As far as I understood, your current Model is built in the following way:

           

          ActivityTimeDuration [min]
          red12:4015
          green13:0520

           

          and you would like to decompose it in the following way:

           

          ActivityTimestamp
          red12:40
          red12:45
          red12:50
          red12:55
          null
          green13:05
          etc.etc.

           

          This will require you to rebuild the data model in the backend script, including a generic Timestamp to ensure the nulls exist.

          • Re: Possible in QlikView?  Polling activity in 10 minute blocks
            Marco Wedel

            Hi,

             

            maybe one solution could be:

             

            QlikCommunity_Thread_198994_Pic1.JPG

            QlikCommunity_Thread_198994_Pic2.JPG

             

            QlikCommunity_Thread_198994_Pic3.JPG

             

            QlikCommunity_Thread_198994_Pic4.JPG

             

             

             

             

            table1:
            LOAD RecNo() as %ActID,
                Time#([Activity Start],'hh:mm') as [Activity Start],
                Interval(Interval#([Activity Total Duration],'m'),'hh:mm') as [Activity Total Duration],
                [Activity Colour]
            Inline [
            Activity Start, Activity Total Duration, Activity Colour
            08:30, 15, Green
            09:30, 20, Green
            10:25, 15, Green
            10:40, 15, Green
            10:55, 25, Green
            11:20, 30, Green
            11:35, 25, Green
            12:25, 40, Green
            12:40, 15, Red
            13:05, 20, Green
            13:30, 15, Green
            13:45, 20, Green
            14:20, 30, Green
            14:50, 20, Green
            15:05, 30, Black
            15:35, 20, Green
            16:10, 30, Green
            16:40, 20, Green
            17:20, 20, Green
            18:00, 12, Black
            ];
            
            table2:
            LOAD %ActID,
                Time,
                Interval#(Interval(RangeMax(RangeMin([Activity End],Time+'00:05')-RangeMax([Activity Start],Time),0),'hh:mm'),'hh:mm') as [Activity Duration];
            LOAD %ActID,
                Time#(Time(Floor([Activity Start],'00:05')+(IterNo()-1)*'00:05','hh:mm'),'hh:mm') as Time,
                [Activity Start],
                [Activity Start]+[Activity Total Duration] as [Activity End]
            Resident table1
            While Time#(Time(Floor([Activity Start],'00:05')+(IterNo()-1)*'00:05','hh:mm'),'hh:mm')<Time#(Time([Activity Start]+[Activity Total Duration],'hh:mm'),'hh:mm');
            

             

            hope this helps

             

            regards

             

            Marco

            • Re: Possible in QlikView?  Polling activity in 10 minute blocks
              Jo Tedesco

              I thought I had replied ... but lost it.

              anyway

              in my head i need something like this:

                    

              Act IDActivity StartActivity DurationDurationActivity endAct ID & Activity End Flag
              18:301508:301y
              18:301558:351y
              18:301558:401y
              18:301558:451y
              29:001009:002y
              29:0010109:052y
              29:0010109:102y
              9:15null null
              9:20null null
              9:25null null
              9:30null null
              9:45null null
              9:50null null
              9:55null null
              310:0060010:003y
              310:00606010:053y
              310:00606010:103y
              310:00606010:153y
              310:00606010:203y
              310:00606010:253y
              310:00606010:303y
              310:00606010:353y
              310:00606010:403y
              310:00606010:453y
              310:00606010:503y
              310:00606010:553y
              310:00606011:00

              3y

              the x axis would be the (Activity end)

              the y axis would the (ActID + Activity End Flag)

              - the flag would be if ( [Activity Start] + [Duration]) <( [Activity Start] + [Activity Duration]) then flag = y

               

              Marco - before I go any further I am going to look at your formulas.  I will need to write them out to understand what is happening.

               

              Thank you for your help so far - will write again.

              Jo