How can I generate a result that would ‘apply’ rank across various columns? Ex:
(a:09:30 11:00 10:00;b:14:00 09:00 15:00;c: 11:00 11:30 12:00)
col d=
0 2 1
1 0 2
0 2 1
and d will be either a string or even integers (no spaces) if easier.
Thx.
Hi Ben,
Are you looking for something like this?
q)t:(a:09:30 11:00 10:00;b:14:00 09:00 15:00;c: 11:00 11:30 12:00)
q)update d:iasc@/: value flip t from t
a b c d
-----------------------
09:30 14:00 11:00 0 2 1
11:00 09:00 11:30 1 0 2
10:00 15:00 12:00 0 1 2
Matthew.
Hi Ben
rank each d should work
Cathal
Apologies, mine should read
q)update d:iasc@/: flip value flip t from t
Hi Ben
I’m going to expand a little on my previous reply. The rank operator (or idesc/iasc) are what you are looking for. For example,
q)a: 5 1 2 4
q)rank a
3 0 1 2
or
q)iasc a
1 2 3 0
q)a iasc a
1 2 4 5
However, you want this applied row by row. If the column names were not an issue you could write this as:
q)update d:rank each t from t
a b c d
-----------------------
09:30 14:00 11:00 0 2 1
11:00 09:00 11:30 1 0 2
10:00 15:00 12:00 0 2 1
Note that here I have assigned the table as param t. However in all likelihood the column names may clash so you may be better doing this:
q)update d:rank each (a;b;c) from t
a b c d
-----------------------
09:30 14:00 11:00 0 2 1
11:00 09:00 11:30 1 0 2
10:00 15:00 12:00 0 1 2
To extend further this could be made into a functional select. To work out the structure of a functional select we use parse
q)parse “update d:rank each (a;b;c) from t”
!
`t
()
0b
(,d)!,(k){x'y};k){$[0h>@x;'rank;<<x]};(enlist;a;b;`c))
q)![t;();0b;(enlistd)!enlist (each;rank;(enlist;a;b;c))]
a b c d
-----------------------
09:30 14:00 11:00 0 2 1
11:00 09:00 11:30 1 0 2
10:00 15:00 12:00 0 2 1
Finally, another alternative for a more readable function is
{update d:rank’[y#t] from t}
with the table as param t and columns to rank as y. I hope this is of some assistance.
Cathal
I’d be careful when using
rank each t
as it will apply rank to dictionaries and not the values themselves. This can be seen if you rename the columns
t:(b:09:30 11:00 10:00;a:14:00 09:00 15:00;c: 11:00 11:30 12:00)
q)update d:rank’[`b`a`c#t] from t
b a c d
-----------------------
09:30 14:00 11:00 2 0 1
11:00 09:00 11:30 0 1 2
10:00 15:00 12:00 2 0 1
We can see what happens as rank is just iasc applied twice
q)rank first t
2 0 1
q)first t
b| 09:30
a| 14:00
c| 11:00
q)iasc first t
bc`a
q)iasc bc`a
2 0 1
Below is an example of how you might apply it to the values
q)update d:rank@/: flip value flip [`b`a`c#t] from t
b a c d
-----------------------
09:30 14:00 11:00 0 2 1
11:00 09:00 11:30 1 0 2
10:00 15:00 12:00 0 2 1
I should probably say that my previous answer worked as a fluke due to there being only three columns.
q)f:{@[x;z;:;rank’[x[;y]]]}
q)f[t;bac;r]
b a c r
09:30 14:00 11:00 0 2 1
11:00 09:00 11:30 1 0 2
10:00 15:00 12:00 0 2 1