I have a table A. I want to get a diff of columns and it’s values based on IDs passed. Basically, based on 2 values of one column I want those columns/values which contains different values.
Hi,
Thank you for your question.
Firstly let me make sure I am understanding your question by use of an example:
For a table t like:
q)t:( id:til 5;a:5#`a;b:5#.Q.a)
q)t
id a b
0 a a
1 a b
2 a c
3 a d
4 a e
you want to pass a function a list of ids, then return which columns contain different values for the rows of the table based on the list of ids passed.
If you are only comparing two ids, then you could use something like:
q){[x;y]where not (~') . select from x where id in y}[t;1 3]
idb
which just selects your desired rows by the ids (1 3) passed, and then returns the columns which differ for those selected rows.
By the nature of the function, it returns the column `id, but this can just be ignored.
If you have a keyed table, then you could do something like:
q)t:1!t
q){[x;y]where not (~') . x y}[t;(id:1 3)]
,`b
which returns only the column that is different excluding the id column.
If you wish to handle more than two ids, you could then use a functional select like:
q)ids:1 3
q)where (?[t;enlist (in;`id;ids);();(cols t)!{(sum;(differ;x))} each cols t]) > 1
idb
Is this what you are after? If not, could you maybe post an example of an input and a desired output to better describe what you are after?
Thanks,
Robert Sketch
Software Developer
AquaQ Analytics
Thanks Robert. You got it right basically I am passing a function a list of ids, then return which columns contain different values for the rows of the table based on the list of ids passed. But with your solution I am getting rank error.
And I want column names with it’s values also
Thanks a lot. You got it right however the solution given is erroring out with “rank”. Also I need to get the column names and their distinct values. So of a column is having distinct values for 2 IDs passed I need the column name and it’s distinct values.
Hello,
Thanks for your reply.
The first two examples I’ve provided only allow for two ids to be passed - anymore than this will throw a “rank” error as you described by the nature of the function. The last example I provided does allow more than two ids to be passed.
However, here is another example which does what I think you are after:
q)ids:1 3 4
q)a:where 1<{sum differ x} each flip m:select from t where id in ids
q)a#m
id b
1 b
3 d
4 e
This code selects the columns that have different values for each id passed, and returns a table with the different columns and its values (as you have described). This can take more than 2 ids.
Written as a function for easier use you could have:
q)ids:1 3 4
q)t:( id:til 5;a:5#`a;b:5#.Q.a)
q)f:{[tab;col;ids] a:where 1<{sum differ x} each flip m:?[tab;enlist (in;col;ids);0b;()]; a#m}
q)f[t;`id;ids]
id b
1 b
3 d
4 e
Where tab is your table, col is your id column, and ids is the list of ids you are interested in.
Is this what you had in mind?
Thanks,
Robert
Thanks for the solution given and it’s working ?now however how can I specify the difference to be based on another column. To be specific I have a column which says long/short , now I want the difference of two versions to be taken by comparing long of one version to the long of another version only and same for short. For now it’s just checking if the count is more than 1.
Hi,
Thanks again for your response.
If I am understanding your requirement correctly, you have a long/short column and want to compare the difference of those values in addition to the ids you pass into the function. I think I have an example of what you’re after:
q)t:( id:til 5;p:longlongshortlongshort;a:5#a;b:5#.Q.a)
q)ids:1 3 4
q)f:{[tab;col1;col2;ids;lsh] a:where 1<{sum differ x} each flip m:?[tab;enlist((in;col1;ids);(=;col2;enlist lsh));0b;()]; a#m}
q)f[t;`id;`p;ids;`short]
id b
2 c
4 e
q)f[t;`id;`p;ids;`long]
id b
0 a
1 b
3 d
It is similar to the first function, but adds two arguments; col2 is the long/short column and lsh specifies if you want to look specifically at long/short values. The other arguments remain the same (col being changed to col1 for clarity).
Is this what you had in mind?
Kind Regards,
Robert Sketch
Software Developer
AquaQ Analytics
You can try using
where column_name like:(“value1”,“value2”) from table .
I suppose your question is to get the multiple values from one column based on ID .