Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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)
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?
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;
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
];
Yes sunny.
My suggestion won't work like your example. I just tested based on the provided example.
hi Sunny,
Thanks for your solution!.
Maybe it's a stupid question, but what if i don't have the column 'Line'?
Greetings,
Polina
Then you can create one using RowNo() or use another unique identifier
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;