Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

polinard
New Contributor

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
MVP
MVP

Re: remove rows based on condition

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

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)  

MVP
MVP

Re: remove rows based on condition

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

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;

MVP
MVP

Re: remove rows based on condition

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

];

Re: remove rows based on condition

Yes sunny.

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

polinard
New Contributor

Re: remove rows based on condition

hi Sunny,

Thanks for your solution!.

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

Greetings,

Polina

MVP
MVP

Re: remove rows based on condition

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

MVP
MVP

Re: remove rows based on condition

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;