9 Replies Latest reply: Jun 18, 2018 5:05 AM by Sunny Talwar RSS

    remove rows based on condition

    Polina Nosulko

      Hello,

       

      Can you help me to fix the following?

       

      initial data:

       

      LineDate + TimePreviousColorNextColorColoraction
      11/06/2018 3:41:41RedGreenGreen
      21/06/2018 3:47:34GreenYellowGreen
      31/06/2018 4:44:59GreenYellowYellowRemove
      41/06/2018 4:45:09 YellowYellowYellowRemove
      51/06/2018 6:45:09YellowYellowYellowRemove
      61/06/2018 6:45:17YellowYellowYellowRemove
      71/06/2018 7:39:17YellowWhiteYellow
      81/06/2018 12:49:55YellowWhite

      White

      Remove
      94/06/2018 8:15:48WhiteWhiteWhiteRemove
      104/06/2018 8:15:59WhiteWhiteWhiteRemove
      114/06/2018 11:00:48WhiteGreenWhite
      124/06/2018 11:01:52WhiteGreenGreenRemove
      134/06/2018 23:04:54GreenGreenGreenRemove
      144/06/2018 23:58:03GreenPurpleGreen

       

      I want

      1. to remove lines where in Columns PreviousColor and NextColorthe values are the same

      2. to keep one line where the lines repeating each other in PreviousColor and NextColor

       

      what I want to get:

       

      LineDate + TimePreviousColorNextColorColor
      11/06/2018 3:41:41RedGreenGreen
      21/06/2018 3:47:34GreenYellowGreen
      71/06/2018 7:39:17YellowWhiteYellow
      114/06/2018 11:00:48WhiteGreenWhite
      144/06/2018 23:58:03GreenPurpleGreen

       

      In this way I see the flow of colors: Red ->Green->Yellow-->White-->Green-->Purple

       

       

      Thank you in advance,

      Polina

        • Re: remove rows based on condition
          Sunny Talwar

          Try this

           

          Table:

          LOAD *,

          PreviousColor&'|'& NextColor as Key

          Where PreviousColor <> NextColor;

          LOAD * INLINE [

              Line, Date + Time, PreviousColor, NextColor, Color

              1, 1/06/2018 3:41:41, Red, Green, Green

              2, 1/06/2018 3:47:34, Green, Yellow, Green

              3, 1/06/2018 4:44:59, Green, Yellow, Yellow

              4, 1/06/2018 4:45:09, Yellow, Yellow, Yellow

              5, 1/06/2018 6:45:09, Yellow, Yellow, Yellow

              6, 1/06/2018 6:45:17, Yellow, Yellow, Yellow

              7, 1/06/2018 7:39:17, Yellow, White, Yellow

              8, 1/06/2018 12:49:55, Yellow, White, White

              9, 4/06/2018 8:15:48, White, White, White

              10, 4/06/2018 8:15:59, White, White, White

              11, 4/06/2018 11:00:48, White, Green, White

              12, 4/06/2018 11:01:52, White, Green, Green

              13, 4/06/2018 23:04:54, Green, Green, Green

              14, 4/06/2018 23:58:03, Green, Purple, Green

          ];


          Right Join (Table)

          LOAD Key,

          Min(Line) as Line

          Resident Table

          Group By Key;

            • Re: remove rows based on condition
              Polina Nosulko

              hi Sunny,

               

              Thanks for your solution!.

               

              Maybe it's a stupid question, but what if i don't have the column 'Line'?

               

              Greetings,

              Polina

                • Re: remove rows based on condition
                  Sunny Talwar

                  Then you can create one using RowNo() or use another unique identifier

                    • Re: remove rows based on condition
                      Sunny Talwar

                      Something like this

                       

                      Table:

                      LOAD *,

                      RowNo() as Line,

                      PreviousColor&'|'& NextColor as Key

                      Where PreviousColor <> NextColor;

                      LOAD * INLINE [

                          Date + Time, PreviousColor, NextColor, Color

                          1/06/2018 3:41:41, Red, Green, Green

                          1/06/2018 3:47:34, Green, Yellow, Green

                          1/06/2018 4:44:59, Green, Yellow, Yellow

                          1/06/2018 4:45:09, Yellow, Yellow, Yellow

                          1/06/2018 6:45:09, Yellow, Yellow, Yellow

                          1/06/2018 6:45:17, Yellow, Yellow, Yellow

                          1/06/2018 7:39:17, Yellow, White, Yellow

                          1/06/2018 12:49:55, Yellow, White, White

                          4/06/2018 8:15:48, White, White, White

                          4/06/2018 8:15:59, White, White, White

                          4/06/2018 11:00:48, White, Green, White

                          4/06/2018 11:01:52, White, Green, Green

                          4/06/2018 23:04:54, Green, Green, Green

                          4/06/2018 23:58:03, Green, Purple, Green

                      ];


                      Right Join (Table)

                      LOAD Key,

                      Min(Line) as Line

                      Resident Table

                      Group By Key;

                • Re: remove rows based on condition
                  Settu Periyasasamy

                  Try this also..

                   

                  T1:
                  LOAD Line,
                  [Date + Time],
                  PreviousColor,
                  NextColor,
                  Color,
                  action
                  FROM
                  [https://community.qlik.com/thread/304548]
                  (
                  html, codepage is 1252, embedded labels, table is @1)
                  Where PreviousColor<>NextColor and Not ( Previous(PreviousColor) = PreviousColor and Previous(NextColor)=NextColor)  

                    • Re: remove rows based on condition
                      Sunny Talwar

                      If I am not wrong, this will only work if the repetition occurs consecutively. If row 3 was at the end, this would have not worked, Right?

                        • Re: remove rows based on condition
                          Settu Periyasasamy

                          You are right Sunny. We need to take the resident with Order by Line. Then, I believe it will work.

                          Like this.

                           

                          LOAD * Resident T1 Where PreviousColor<>NextColor and

                          Not ( Previous(PreviousColor) = PreviousColor and Previous(NextColor)=NextColor) 

                          Order by Line asc;

                            • Re: remove rows based on condition
                              Sunny Talwar

                              Well, my concern was more like this... what if the data looked like this (swapped row 3 and 14, but kept the line number same)...

                               

                              LOAD * INLINE [

                                  Line, Date + Time, PreviousColor, NextColor, Color

                                  1, 1/06/2018 3:41:41, Red, Green, Green

                                  2, 1/06/2018 3:47:34, Green, Yellow, Green

                                  3, 4/06/2018 23:58:03, Green, Purple, Green

                                  4, 1/06/2018 4:45:09, Yellow, Yellow, Yellow

                                  5, 1/06/2018 6:45:09, Yellow, Yellow, Yellow

                                  6, 1/06/2018 6:45:17, Yellow, Yellow, Yellow

                                  7, 1/06/2018 7:39:17, Yellow, White, Yellow

                                  8, 1/06/2018 12:49:55, Yellow, White, White

                                  9, 4/06/2018 8:15:48, White, White, White

                                  10, 4/06/2018 8:15:59, White, White, White

                                  11, 4/06/2018 11:00:48, White, Green, White

                                  12, 4/06/2018 11:01:52, White, Green, Green

                                  13, 4/06/2018 23:04:54, Green, Green, Green

                                  14, 1/06/2018 4:44:59, Green, Yellow, Yellow

                              ];