10 Replies Latest reply: Oct 11, 2017 3:06 AM by Marcus Sommer RSS

    Range above function in load function.

    Marko Zadravec

      Hi,

      I am quite new to Qlik Sense, but I have some interesting problem, that I can't solve and I need your help.

       

      I have a field that every 0.n second save 1 or 0 in database. So my data looks like a very long stream of 1 and 0.

       

      Inside qlik sense I can detect when status has changed from 0 to 1, like this:

       

           Load

           [id],

           [time_stamp],

           (If(status = '1' and Previous(status) = '0', 1, 0)) as Change;

       

      My problem is, that there could be mild "errors", when status would change very quickly from 1 to 0 and back, like this:

      [0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,1,0,0,1,1,1,1,0,0,0,0,0,0,0,0]. I would like to detect this as only one change. (from 0 to 1)

       

      So, my instinct was to check if previous n values are all zero and current value is 1. I saw that there is ABOVE function, but only for chart analysis, not for script function (load function). How to handle this? I would need something like:

       

           Load

           [id],

           [time_stamp],

           (If(status = '1' and {sum of previous 20 status values} = '0', 1, 0)) as Change;

       

      Even more, the second solution that I am looking for, and I would ask your help for, is to not rely on number of values, but rather on time stamp. So that function would be:

       

           Load

           [id],

           [time_stamp],

           (If(status = '1' and {sum of all status values from time_stamp to time_stamp-2s} = '0', 1, 0)) as Change;

       

      Please help.

        • Re: Range above function in load function.
          Marco Wedel

          Hi,

           

          one solution might be nested Previous() functions:

           

          QlikCommunity_Thread_277280_Pic1.JPG

           

          table1:
          LOAD id,
              status,
              If(status = 1 and
                  Previous(status)=0 and
                  Previous(Previous(status))=0 and
                  Previous(Previous(Previous(status)))=0 and
                  Previous(Previous(Previous(Previous(status))))=0 and
                  Previous(Previous(Previous(Previous(Previous(status)))))=0,
                  1,
                  If(status = 0 and
                    Previous(status)=1 and
                    Previous(Previous(status))=1 and
                    Previous(Previous(Previous(status)))=1 and
                    Previous(Previous(Previous(Previous(status))))=1 and
                    Previous(Previous(Previous(Previous(Previous(status)))))=1, 
                    0,
                    Alt(Peek(Change),status)
                    )
                  ) as Change;
          LOAD RecNo() as id,
              Round(Rand()) as status
          AutoGenerate 300;
          

           

          but as your requested function already triggers on even the smallest glitches after a stable status period this is probably not what you really intended ?!?!

           

          hope this helps nevertheless

           

          regards

           

          Marco

          • Re: Range above function in load function.
            Henry Esparza

            If(STATUS = '1' and sum({<[TIMESTAMP] = {"<=$(=[TIMESTAMP]))>=$(=[TIMESTAMP]-'0:00:02')"}>}[VALUES])='0',1',0)) as Change

             

            Hope this helps

              • Re: Range above function in load function.
                Marko Zadravec

                Hi Henry,

                Thank you for your answer, but I didn't understand it fully.

                My first comment is, that as it seems to me, you can't use {} , $, in load function. (At least your code, gave me an error at first {.)

                My second question is who should this work? Sum all values where timestamp = timestamp >= timestamp - 2s?

                I don't understand how would program know which timestamp is which.

                Could you explain your solution a little bit further?

              • Re: Range above function in load function.
                Marcus Sommer

                I would rather go with something like this to identify the last changes and/or how many changes happened within the last n records:

                 

                Load

                [id],

                [time_stamp],

                if(id = previous(id), rangesum(peek('RowNoID'), 1), 1) as RowNoID,

                ceil(peek('RowNoID') / 20) as RowNoIDCluster,

                If(status <> Previous(status) = 0, peek('SumChange') + 1, peek('SumChange')) as SumChange,

                If(status <> Previous(status), rowno(), peek('LastChange')) as LastChange;

                 

                and for grouping them to a certain time-period I would tend to round the timestamp appropriate with something like:

                 

                ceil(timestamp, 1/24/60/60)

                 

                to round up to full seconds.

                 

                It's not tested just some ideas and you will need to extend this kind of logic to a proper sorting and maybe also including some more checkings within the expressions - but I think the general logic will work. More to peek and previous could you find here: Peek() or Previous() ?

                 

                - Marcus

                  • Re: Range above function in load function.
                    Marko Zadravec

                    Thanks for your answer. I think it would be the right answer.

                    My new problem is that I can't use your calculated field

                     

                     

                        (If(id <> Previous(id) and id=1, peek('SumChange') + 1, 0)) as SumChange,

                        (If(id <> Previous(id), rowno(), peek('LastChange'))) as LastChange,

                        (If(SumChange=1 and LastChange - Previous(LastChange)>20, 1,0)) as numberOfLifts;

                     

                     

                    and I get error :

                     

                     

                    The following error occurred:

                    Field 'SumChange' not found

                      • Re: Range above function in load function.
                        Marcus Sommer

                        SumChange is just created in this load and therefore it couldn't directly accessed but a peek('SumChange') would work whereby in this case you would need to use the expression which creates SumChange and LastChange - this meant:

                         

                        ...

                        If(

                             If(id <> Previous(id) and id=1, peek('SumChange') + 1, 0) =1 and

                             If(id <> Previous(id), rowno(), peek('LastChange')) - Previous(LastChange)>20,

                        1,0)) as numberOfLifts

                        ...

                         

                        - Marcus