I have a column of type C. How do I compare the value to the previous value in the same column? I did col1 like prev col1 but it returns a Correction hint: length error. I also created another column newCol: prev col1 but still cannot perform the comparison. I also tried with = and no luck. How can I do this?
a sample data:
col1
Paris
London
London
New York
Singapore
Ha Noi
Could you use the prior keyword?
q)t
col1
-----------
"Paris"
"London"
"London"
"Ney York"
"Singapore"
"Ha Noi"
q)select (~) prior col1 from t
col1
----
0
0
1
0
0
0
When comparing strings, if they are the same length it will check that each character in each slot of the array is the same, and return a list of booleans to tell you where the strings are the same. If the strings are two different lengths, you will get a length error. If you want to test if two strings are the exact same thing, you can use ~, which will work regardless of the length of the string and give you a single boolean telling you if they are the same.
Use each prior:https://code.kx.com/q/ref/maps/#each-prior
With match: https://code.kx.com/q/basics/comparison/#match
q)tab:([]col1:("Paris";"London";"London";"New York"))
q)select col1,compare:(~':)col1 from tab
col1 compare
------------------
"Paris" 0
"London" 0
"London" 1
"New York" 0
You should use like' instead of like, because you are comparing not to single value, but to list.
update comparison: col1 like' prev col1 from
([]col1:("Paris";"London";"London";"New York";"Singapore";"Ha Noi"))
Although this is essentially the same as Matthews and jomahony's answers, the differ keyword can arguably make it easier to read/understand:
q)select not differ col1 from ([]col1:("Paris";"London";"London";"New York"))
col1
----
0
0
1
0