topic Re: remove rows based on condition in QlikView

remove rows based on condition

PolBe — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Can you help me to fix the following?

initial data:

Line	Date + Time	PreviousColor	NextColor	Color	action
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	Remove
4	1/06/2018 4:45:09	Yellow	Yellow	Yellow	Remove
5	1/06/2018 6:45:09	Yellow	Yellow	Yellow	Remove
6	1/06/2018 6:45:17	Yellow	Yellow	Yellow	Remove
7	1/06/2018 7:39:17	Yellow	White	Yellow
8	1/06/2018 12:49:55	Yellow	White	White	Remove
9	4/06/2018 8:15:48	White	White	White	Remove
10	4/06/2018 8:15:59	White	White	White	Remove
11	4/06/2018 11:00:48	White	Green	White
12	4/06/2018 11:01:52	White	Green	Green	Remove
13	4/06/2018 23:04:54	Green	Green	Green	Remove
14	4/06/2018 23:58:03	Green	Purple	Green

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:

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
7	1/06/2018 7:39:17	Yellow	White	Yellow
11	4/06/2018 11:00:48	White	Green	White
14	4/06/2018 23:58:03	Green	Purple	Green

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 — Tue, 12 Jun 2018 16:13:18 GMT

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

settu_periasamy — Tue, 12 Jun 2018 19:24:04 GMT

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 — Tue, 12 Jun 2018 19:28:06 GMT

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_periasamy — Tue, 12 Jun 2018 19:35:57 GMT

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 — Tue, 12 Jun 2018 19:42:24 GMT

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

settu_periasamy — Wed, 13 Jun 2018 18:48:32 GMT

Yes sunny.

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

Re: remove rows based on condition

PolBe — Mon, 18 Jun 2018 08:31:43 GMT

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 — Mon, 18 Jun 2018 09:04:39 GMT

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

Re: remove rows based on condition

sunny_talwar — Mon, 18 Jun 2018 09:05:47 GMT

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;