Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
PolBe
Contributor III
Contributor III

remove rows based on condition

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

9 Replies
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;

settu_periasamy
Master III
Master III

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)  

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?

settu_periasamy
Master III
Master III

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;

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

];

settu_periasamy
Master III
Master III

Yes sunny.

My suggestion won't work like your example. I just tested based on the provided example.

PolBe
Contributor III
Contributor III
Author

hi Sunny,

Thanks for your solution!.

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

Greetings,

Polina

sunny_talwar

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

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;