7 Replies Latest reply: Jul 15, 2013 8:23 AM by rohit koul RSS

    Logic Peek above

    rohit koul

      Dear All,

       

      I have situation where I have to create a Flag so that I can continue with Mapping part.

       

      Consider the Example (Attached EXCEL)

       

       

      Column1Column2
      A0
      B1
      C1
      D1
      E0
      F1
      G1
      H1
      I1
      J0
      K1
      L1
      M1
      N1
      O0
      P0
      Q0
      R1
      S0
      T1
      U1
      V1
      W1

       

       

      I have to Mark a flag when in Column 2 data '1' appears 4 times continuesly so my Output should have F,G,H,I,K,L,M,NT,U,V,W Column 1 with Flag as 1 rest zero.

       

      Something like this

       

      Column1Column2OUTPUT_Flag
      A00
      B10
      C10
      D10
      E00
      F11
      G11
      H11
      I11
      J00
      K11
      L11
      M11
      N11
      O00
      P00
      Q00
      R10
      S0
      T11
      U11
      V11
      W11

      if possible I need to do it from Back end.

       

      I tried from front end using Below function

      if(RangeSum((below(sum(No_Of_Customer_Flag),0)),

                (below(sum(No_Of_Customer_Flag),1)),

                (below(sum(No_Of_Customer_Flag),2)),

                (below(sum(No_Of_Customer_Flag),3)))=4,QuoteBuckets, 0)

       

      but didn't worked also tried some logic with Backend.

       

      with Peek and Previous fuction but still the same

        • Re: Logic Peek above
          Stefan Wühl

          Maybe like this?

           

          INPUT:

          LOAD RecNo() as RecID,

               Column1,

               Column2

          FROM

          [Data-1.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          TMP1:

          LOAD Column1,

               Column2,

               if(Column2 = 1 and peek(Column2) <> 1, RecID, peek(Start)) as Start,

               RecID

          Resident INPUT order by RecID asc;

           

          Drop table INPUT;

           

          TMP2:

          LOAD Column1,

               Column2,

               Start,

               if(Column2 = 1 and peek(Column2) <> 1, RecID, peek(End)) as End,  

               RecID

          Resident TMP1 order by RecID desc;

           

          drop table TMP1;

           

          RESULT:

          LOAD Column1,

               Column2,

               if(End-Start = 3 and Column2, 1,0) as Result

          Resident TMP2 order by RecID;

           

          drop table TMP2;

            • Re: Logic Peek above
              rohit koul

              THanks Mr swuehl that was a great help but the logic will not work if my data will get change like If Records B,C,D,E will be 1 then Flag shoud come as 1 but it is not working:

               

              Column1Column2
              A0
              B1
              C1
              D1
              E1
              F1
              G1
              H0
              I1
              J0
              K1
              L1
              M1
              N1
              O0
              P0
              Q0
              R1
              S0
              T1
              U1
              V1
              W1

              I need a Dynamic Way to get a flag where ever I will get 4 continues 1

               

              i have attached the App and Excel for help

                • Re: Logic Peek above
                  Stefan Wühl

                  Not really sure if I understand.

                   

                  Previously, I assumed you want to flag a series of exactely 4 consecutive 1, not less than 4, not more than 4.

                   

                  Ok, I think you want to flag at least 4 consecutive 1 values, right?

                   

                  You just need to change the last load, where the number of consecutive 1 values is checked:

                   

                  RESULT:

                  LOAD Column1,

                       Column2,

                      if(End-Start >= 3 and Column2, 1,0) as Result

                  Resident TMP2 order by RecID;

                   

                  But this will not match your OUTPUT_Flag column in your above attached Excel:

                   

                  Column1Column2OUTPUT_Flag
                  A010
                  B120
                  C130
                  D140
                  E150
                  F161
                  G171
                  H081
                  I191
                  J0100
                  K1111
                  L1121
                  M1131
                  N1141
                  O0150
                  P0160
                  Q0170
                  R1180
                  S019
                  T1201
                  U1211
                  V1221
                  W1231

                   

                   

                  Can you elaborate on why B,C,D,E are not flagged, while H and I are?

                    • Re: Logic Peek above
                      rohit koul

                      Sorry by Mistake I didnt changed that out put i have removed that excel.

                       

                       

                      Only Out Put i need:

                       

                      I want to flag only those Records where  4 consecutive 1 values are comming.

                       

                      So above out put should like for B,C,D,E then K,L,M,N last T,U,V,W for attached File

                       

                      I tried your logic but didn't work You can find the reviewd excel attached here

                        • Re: Logic Peek above
                          Stefan Wühl

                          Hm, seems I still don't get your logic.

                           

                          only those Records where  4 consecutive 1 values are comming.

                           


                          This should include F,G, shouldn't it (because D,E,F,G are four 1 values in sequence.

                           

                          If you are interested only in the first 4 of such a sequence (B,C,D,E are first 4 values of sequence B,C,D,E,F,G), then you can use

                           

                          RESULT:

                          LOAD Column1,

                               Column2,

                               if(End-Start >= 3 and Column2 and RecID-Start <=3, 1,0) as Result // doubt

                          Resident TMP2 order by RecID;

                           

                          If you are interested in something else, then I it seems I don't understand. But you should be able to simply enhance the logic I suggested.

                           

                          Regards,

                          Stefan