21 Replies Latest reply: Feb 3, 2017 8:21 AM by Aurélien Martinez RSS

    While loop

    Prateek Arora

      Hello All,

       

      I have the below data with me wherein I am required to calculate the no. of  violations in a day.  The actual value should lie between the min and max value. If it doesn't, it should be counted as an violation.  In the below case, the no. of violation for 01-01-2017 should be 2 because it is a continuous violation till it stabilizes once and then again violates (so count 2) and likewise for 02-01-2017 should be 1 (continuous violation)

       

      ParameterDateTimeMin ValueActual ValueMax Value

      Temperature

      01-01-2017 12:15:00 AM16

      15

      28
      Temperature01-01-2017 02:30:00 AM161528
      Temperature01-01-2017 05:42:00 AM1614.828
      Temperature01-01-2017 07:20:00 AM1615.228
      Temperature01-01-2017 09:00:02 AM 162328
      Temperature01-01-2017 11:59:00 AM161428
      Temperature02-01-2017 01:12:00 AM 162928
      Temperature02-01-2017 05:12:00 PM1628.828

       

      I tried with a couple of do while loops but couldn't get the logic to include timestamp function to mark the end of the day and count afresh for a new day.

       

      Regards

        • Re: While loop
          Aurélien Martinez

          Hi,

           

          the script:

           

          Data:

          LOAD Parameter,

               DateTime,

               Date#(Subfield(DateTime, ' ', 1), 'DD-MM-YYYY') as Date,

               [Min Value],

               [Actual Value],

               [Max Value],

               If([Actual Value]<[Min Value] or [Actual Value]>[Max Value], 1, Null()) as test

          FROM

          [https://community.qlik.com/thread/247238]

          (html, codepage is 1252, embedded labels, table is @1);

           

          data2:

          LOAD

            *,

            if(IsNull(Peek(test2)) and Peek(Date) = Date, 1) as no

          ;

          LOAD

            *,

            if(test and Peek(Date) = Date, 1) as test2

          Resident Data

          Order by DateTime desc

          ;

           

           

          DROP Tables Data;

           

          In your chart:

          sum(no)

           

          Result;

           

          Date sum(no)
          3
          01-01-20172
          02-01-20171
            • Re: While loop
              Malik Shaik

              Hi Aurelien,

               

              Can you please help me out here,

               

              i am using the RangeSum(Above(Sum([Revenue]),0,RowNo())) for the CummulativeSum for the Revenue.

               

              It is splitted by week1,week2,week3 and so on.

               

              Three columns Weeks, Sum(Revenue) & CummulativeSum

               

              When i select Week 1 or Week 2 the Sum(Revenue) and CummulativeSum values becomes equal

               

              Is there any solution for this so that when i select Week2 it should show me the cummulative value for Week2 ?

              • Re: While loop
                Prateek Arora

                Hi Aurelien,

                 

                Can you please explain your code from data2 table? I didnt understand the two load functions that you have used ?

                 

                 

                Regards

                  • Re: While loop
                    Aurélien Martinez

                    Hi,

                     

                    I have used Preceding Load, but you can write :

                     

                    data2:

                    LOAD

                      *,

                      if(test and Peek(Date) = Date, 1) as test2

                    Resident Data

                    Order by DateTime desc

                    ;

                     

                    data3:

                    LOAD

                      *,

                      if(IsNull(Peek(test2)) and Peek(Date) = Date, 1) as no

                    Resident data2

                    ;

                     

                     

                    DROP Tables Data, data2;

                      • Re: While loop
                        Prateek Arora

                        Oh Okay.. !! Thanks Aurelien. But what I fail to understand is when I use the same logic with the data in my excel file, the count doesn't seem to work. I have attached a sample of the excel and the qvf.

                          • Re: While loop
                            Aurélien Martinez

                            It's just a date issue

                             

                            ActualValues:

                            LOAD

                                Parameter,

                                DateTime,

                                Date(Floor(Subfield(DateTime, ' ', 1))) as Date,

                                "Min Value",

                                "Actual Value",

                                "Max Value",

                                 If("Actual Value"<"Min Value" or "Actual Value">"Max Value", 1, Null()) as test

                            FROM [lib://amartinez35/Sample.xlsx]

                            (ooxml, embedded labels, table is Sheet1);

                             

                             

                             

                             

                            data2:

                            LOAD

                              *,

                              if(IsNull(Peek(test2)) and Peek(Date) = Date, 1) as "VCount"

                            ;

                            LOAD

                              *,

                              if(test and Peek(Date)*1 = Date*1, 1) as test2

                            Resident ActualValues

                            Order by DateTime desc

                            ;

                             

                             

                            DROP Tables ActualValues;

                              • Re: While loop
                                Prateek Arora

                                But that doesnt give me the right count of violation. The violation should only be counted when the temperature stabilizes or if it is a continuous violation. So the correct count for the dates 01/12, 02/12, 03/12, 04/12 should have been 2, 2, 2, 0 respectively and not what is displayed.

                                  • Re: While loop
                                    Aurélien Martinez

                                    ActualValues:

                                    LOAD

                                        Parameter,

                                        DateTime,

                                        Date(Floor(Subfield(DateTime, ' ', 1))) as Date,

                                        "Min Value",

                                        "Actual Value",

                                        "Max Value",

                                         If("Actual Value"<"Min Value" or "Actual Value">"Max Value", 1, Null()) as test

                                    FROM [lib://amartinez35/Sample.xlsx]

                                    (ooxml, embedded labels, table is Sheet1);

                                     

                                     

                                    data2:

                                    LOAD

                                      *,

                                      if(IsNull(Peek(test2)) and Peek(Date) = Date and test, 1) as "VCount"

                                    ;

                                    LOAD

                                      *,

                                      if(test and Peek(Date)*1 = Date*1, 1) as test2

                                    Resident ActualValues

                                    Order by DateTime desc

                                    ;

                                     

                                     

                                    DROP Tables ActualValues;

                                     

                                    but for the 04/12 is a continuous violation ?

                                      • Re: While loop
                                        Prateek Arora

                                        Yes, for continuous violation, count 1. So 04/12 is 1. Somehow your code works magic for smaller data set but when I try to do the same for huge data entries, it flops :(

                                          • Re: While loop
                                            Aurélien Martinez

                                            In the field "Parameter" do you have other value than "Temperature Value" ?

                                              • Re: While loop
                                                Prateek Arora

                                                Hi Aurelien,

                                                 

                                                Yes, I do have another parameter value and also another column named Device ID. So I wish to calculate the violation for each device id for all days. Here's the actual file attached.

                                                 

                                                 

                                                Regards

                                                  • Re: While loop
                                                    Aurélien Martinez

                                                    Hi,

                                                     

                                                    ActualValues:

                                                    LOAD

                                                        Parameter,

                                                        DateTime,

                                                        Date(Floor(Subfield(DateTime, ' ', 1))) as Date,

                                                        "Min Value",

                                                        "Actual Value",

                                                        "Max Value",

                                                         If("Actual Value"<"Min Value" or "Actual Value">"Max Value", 1, Null()) as test

                                                    FROM [lib://amartinez35/Sample.xlsx]

                                                    (ooxml, embedded labels, table is Sheet1);

                                                     

                                                     

                                                    data2:

                                                    LOAD

                                                      *,

                                                      if(IsNull(Peek(test2)) and Peek(Date) = Date and test and Parameter=Peek(Parameter),, 1) as "VCount"

                                                    ;

                                                    LOAD

                                                      *,

                                                      if(test and Peek(Date)*1 = Date*1 and Parameter=Peek(Parameter), 1) as test2

                                                    Resident ActualValues

                                                    Order by DateTime, Parameter desc

                                                    ;

                                                      • Re: While loop
                                                        Prateek Arora

                                                        Thanks for replying Aurelien.. I did try this earlier but this code seems to work fine only for some 'Device ID' whereas for others, the violation count displayed is incorrect..What could be the possible reason ? Am I missing out something ?

                                                          • Re: While loop
                                                            Aurélien Martinez

                                                            ActualValues:
                                                            LOAD
                                                            [Device ID],
                                                            Parameter,
                                                            DateTime,
                                                            Date(Floor(Subfield(DateTime, ' ', 1))) as Date,
                                                            "Min Value",
                                                            "Actual Value",
                                                            "Max Value",
                                                            If("Actual Value"<"Min Value" or "Actual Value">"Max Value", 1, Null()) as test
                                                            FROM
                                                            [W:\HOME\QLIKVIEW\Bordel\Violations.xlsx]
                                                            (
                                                            ooxml, embedded labels, table is Sheet1);

                                                            data2:
                                                            LOAD
                                                            *,
                                                            if(IsNull(Peek(test2)) and Peek(Date) = Date and test and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as "VCount"

                                                            ;

                                                            LOAD
                                                            *,
                                                            if(test and Peek(Date)*1 = Date*1 and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as test2

                                                            Resident ActualValues

                                                            Order by [Device ID], Parameter, DateTime  desc
                                                            ;

                                                            DROP Table ActualValues

                                                              • Re: While loop
                                                                Prateek Arora

                                                                Thanks man! This is wonderful! Thanks a lot. What if I want to calculate duration of violation in hrs and minutes?

                                                                  • Re: While loop
                                                                    Aurélien Martinez

                                                                    Hi,

                                                                     

                                                                    ActualValues:
                                                                    LOAD
                                                                    [Device ID],
                                                                    Parameter,
                                                                    DateTime,
                                                                    Date(Floor(Subfield(DateTime, ' ', 1))) as Date,
                                                                    "Min Value",
                                                                    "Actual Value",
                                                                    "Max Value",
                                                                    If("Actual Value"<"Min Value" or "Actual Value">"Max Value", 1, Null()) as test
                                                                    Resident data2
                                                                    ;

                                                                    data:
                                                                    LOAD
                                                                    *,
                                                                    if(IsNull(Peek(test2)) and Peek(Date) = Date and test and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as "VCount"
                                                                    ;
                                                                    LOAD
                                                                    *,
                                                                    if(test and Peek(Date)*1 = Date*1 and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID], 1) as test2
                                                                    Resident ActualValues
                                                                    Order by [Device ID], Parameter, DateTime  desc
                                                                    ;

                                                                    data3:
                                                                    LOAD
                                                                    *,
                                                                    Time(if(test and Peek(Date)*1 = Date*1 and Parameter=Peek(Parameter) and Peek([Device ID])=[Device ID]DateTime - Peek(DateTime) + Alt(Peek(time2), 0))) as time2
                                                                    Resident data
                                                                    Order By [Device ID], Parameter, DateTime  asc
                                                                    ;

                                                                    DROP Table ActualValues, data2, data;

                                        • Re: While loop
                                          Vineeth Pujari

                                          Try

                                          RangeSum(Above(Sum({<Week>}[Revenue]),0,RowNo()))

                                          • Re: While loop
                                            Prajna Alva

                                            Hi Anshu,

                                             

                                             

                                            LOAD Parameter,
                                            DateTime ,
                                            MinValue,
                                            ActualValue,
                                            MaxValue,
                                            if(ActualValue<=MaxValue and ActualValue>=MinValue,0,1) as Voilation
                                            FROM
                                            [..\..\..\Users\\Desktop\Temperature.txt]
                                            (
                                            txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                                             

                                            In the chart you can add

                                             

                                            Dimension -=Date#(Subfield(DateTime, ' ', 1), 'DD-MM-YYYY')

                                            Measure - sum(Voilation)

                                             

                                            Hope this helps you.

                                             

                                            Thanks,

                                            Prajna