// simple query viewer // query2 ts/series ts/value tables:{[t;u] / read tables from directory T::2:t / read index table T..l::t / index table name N::(^T[])1 / cardinality f:!T / fields K::f@&~-2=4::'T[] / keys F::f _dvl K / data are the rest T[.;`e]:0 / no editing T[~K;`bg]:858585 / key field background T[~K;`fg]:{:[(*|.k.Q.vn _v)_in Q.P.G;107010;101070]} / key field foreground T[~F;`bg]:999999 / data field background T[~K,F;`k]:(#K,F)#,".k.V.J:*_i" / selected row T[~K,F;`kl]:(#K,F)#,".k.V.up[]" / sort by field U::2:u / read value table U..l::u / value table name G::!U / id, date, value show[] / show } show:{`show$'`.k.V`.k.Q;} / show control, index, value query:{ / evaluate stored query rs:{[l]Q.I::();{Q.J::x;Q.I,:_n;Q.eval .'y}'[!#l;l]} / restrict wh:{f:`$x;o:Q.O($Q.O)?y;(f;o;:[4=4:o;z;(*0#T`$x)$z])} / construct query log wh:{f:`$x;o:Q.O($Q.O)?y;(f;o;:[4=4:o;z;. z])} / construct query log ct:{1_'(&(*x)=*:'y)_ y:(,x),y} / generalized cut if[_n~x;:Q.S] / return query q:,/" ",'x / flatten query q:ct[" ";q]_dv"" / chop into words i:q?"FROM" / find projections p:1_ i#q;q:i _ q / get projections p::[p~,,"*";2#,0#`;+{-2#`,`$x}'(&(~<)':0,p _lin$1_ Q.P.O)_ p] / construct projections t:*1_ q;q:2_ q / get table i:q?"WHERE";w:(i+1)_ q;q:i#q / find where clause g::["GROUP"~*q;`$2_ q;0#`] / construct group by w::[#w;ct[,"&"]'ct[,"|";w];()] / construct where Q.P[`G`A`V]:(,g),p / insert projection Q.L::wh .''w / insert restriction :[#Q.L;rs Q.L;Q.J::-1+#Q.I::,_n] / restrict from query Q.show[] / show restriction } \d V / index table view vsl:{[v;j]:[j~_n;.();fs@.+(!v;v[;j];{.+(`e`bg`fg`l;x)}'v[.;`e`bg`fg`l])]} / slice row from index view wsl:{[u;k]fs@{.[x;(.;y);:;z]}/[.+(!u;u[;k]);`l`bg`e;(u[.;`l];u[.;`bg][0;k];0)]} / slice row from value view fs:{[d].[d;(.;`f);:;FS]} / format slice tvw:{[t;i;v;g;a]:[#v;at[.+(v;t[v;i];vl[t@~v;v;a]);v;g;a];@[t;_n;@[;i]]]} / view table definition vl:{[t;v;a].[t;(;`l);:;.k.Q.qf'[a;v]]} / view field labels ui:{[i]:[~(?i)~,_n;{x@#y$:;y;x#y]}[30] / format slice values ct:{[v;l;r;c;R;C;s;d] / simulate cross/pivot a:?R / unique rows b:?C / unique cols r@:(=R)[;0] / row contents c@:(=C)[;0] / col contents m:((#b),#a)#0n / matrix of nulls j:(^m)_sv(b?/:C;a?/:R) / non-null indices into ,/m m:(^m)#@[,/m;j;:;v] / populate m with values m:(,s r),m / first row is unique row values m:.+(`$"f",/:$!1+#b;m) / fields are unique col values + 1 m:@[m;_n;@[; x.y (over) dj:{:[x=0N;`;,/@[$1!0 100 100 _vs _dj x;0 1;1!"/",-2#"0",]]} / julian to date format jd:{:[`~x;0N;_jd 100_sv -1!0$1_'(&x="/")_ x:"/",x$:]} / date to julian conversion uvw:{[u;t;r;x] / value table view if[~#i:&u[F]_lin t F;:.()] / where value.id = index.id if[~#i@:range[i;u[D;i]]. r`From`To;:.()] h:`$dot/$(),g::[3>#f:!t;f;-2#f] / row component fields k:`$dot/'$+t[g;t[F]?/:u[F;i]] / row fields ct[u[V;i];(h,`date)x].,/x!'((k;u[D;i]);(u[F;i];u[D;i]);(::;`$dj'))} / present as cross/pivot range:{[i;d;f;t] / restrict dates if[~f=0N;i@:&~d= i} / new index X..d:".k.T;0" / transpose bit U..kl:"X~:" / transpose on click U..d:"uvw[.k.U;@[T;F;:;.k.T[F;I]];R;X]" / view is a dependency U..l..d:".k.U..l" / value table label W..d:":[_n~K;.();wsl[U _di`f0;K]]" / slice W..l..d:":[~_n~.k.V.K;$.k.V.U[`f0;.k.V.K]]" / label R..d:".k.T;.+(`From`To;0N;.+(`f`g;(.k.V.dj;.k.V.jd)))" / date range R..l:"Date range" / label \d ^ V..c:`form / form V..a:((`U;`W`R);`T`S) / arrangement V..x:90 / width V..y:40 / height V..l:`View / label \d Q / query window I..d:".k.T;,_n" / initial row selection = _n = all J..d:".k.T;0" / index into J v_..d:i_..d:".k.T;" / current _v, _i restrict_:{if[(#I J)&~(z;x;y)_in L J;eval[z;x;y];L[J],:,(z;x;y)]} / restrict eval:{I[J]@:&{:[4=4:x;.k.Q x;x]}[y][.k.T[x;I J];z];show[]} / evaluate, e.g.: &=[T.f;10] show:{V[]:su'.k.T[.k.K;I J];R::} / show restriction restrict:{v:*|vn v_;d:v_ . i_;if[~d _in V v;or[]];restrict_[=;d;v]} / global restrict set_vi:{v_::_v;i_::_i} / set v_, i_ set_v:{v_::_v;i_::_n} / set v_ vn:{`$1_'(&x=".")_ x$:} / `.a.b.c -> `a`b`c bg:{858585 999999 x _in V@*|vn _v} / gray-out values not in restriction su:({x@ v unproject:{i_::();A::A _di _i;G::G _dv V ._i;V::V _di _i} / double-click to move v -> t (remembers order in t) aggr:{if[(~A[x]=*O)|#G;A[x]:O(1+O?A x)!#O]} / if grouped, cycle aggregation A..d:G..d:".k.K;0#`" / aggregation, group O:``avg`sum`count / aggregation operators by:{:[x _in G;G::G _dv x;G,:x];.k.Q.I::} / toggle group by gp:{if[~@i_;i_::0];:[V[i_]_in .k.K;by V i_;aggr i_];V::} / group/aggregate fg:{:[~y _in .k.K;0;x&y _in G;107010;101070]} / foreground vf:{-20$:[O[0]=f:A ._i;x;($f)," ",$x]} / format U..d:".k.K,.k.F" / initialize unprojected V..d:".k.K;0#`" / initialize v to none V..k:"select[]" / callbacks, arrangements, &c. U..fg:fg[0] / t foreground V..fg:fg[1] / v foreground U..kk:"project[]" / t -> v V..kk:"unproject[]" / v -> t V..f:vf / v format U..l:`Unprojected / label V..l:`Projected / label u:"up[]";u..l:`Up / up d:"dn[]";d..l:`Down / down g:"gp[]";g..l:`Group / group aggregate r:"reset[]";r..l:`Reset / reset projection g..c:r..c:u..c:d..c:`button / buttons \d ^ P..c:`form / form P..l:`Project / label P..a:(`U`V;`u`d`g`r) / arrange query:{[l;v] / construct query r::[~#v;,"SELECT * FROM ",$.k.T..l;(,"SELECT"),qf'[P.A;P.V],,"FROM ",$.k.T..l] / select .. from .. if[#P.G;r,:(,"GROUP BY")," ",'$P.G] / group by .. if[#l;r,:(,"WHERE "),.[,/{.[" & ",/:x;0 1;:;"|"]}'wr .''l;0 1;:;" "]] / where .. r} qf:{" ",:[x=*P.O;$y;($x)," ",$y]} / format query fields wr:{($x)," ",($y)," ",5:z} / format where relations sw:{[i]if[i>j:(S _sm\:"*WHERE*")?1;J::(1+j+\#:'L)_bin i;show[]]} / select where clause L..d:".k.K;,()" / restriction log S..d:"query[L _dv();P.V]" / query <- restriction, projection S..k:"sw@*_i" / select where clause S..e:0 / edit S..l:`Query / no label reset_all:{I::,_n;J::0;L::,();vi[]} / reset all restrictions reset:{I::I _di J;L::L _di J;J::0|J-1;vi[]} / reset current restriction or:{if[#L J;I,:_n;L,:,();J+:1;vi[]]} / add an or-clause vi:{v_::i_::_n;V[]:D[];R::} / reset control, data oper:{if[~_n~i_;restrict_[x;v_ . i_;*|vn v_]]} / v o v_ . i_ oper2:{if[~_n~v_;restrict_[x;y;*|vn v_]]} / v o d like:{($x)_sm\:y} / like function lk_l:{:[x~_n;"";$*|vn x]," ",($O 6),":"} / like label lk_d:{:[x~_n;"";y~_n;"";$x . y]} / like value O:(=;<;>;~=;~>;~<;`like) / operators B:`eq`lt`gt`ne`le`ge / button names button:{@[~.[B x;();:;"oper[O ",($x),"]"];`c`l;:;(`button;$O x)]} / make button button'!#B; / buttons lk..d:".k.Q.lk_d[.k.Q.v_;.k.Q.i_]" / like value lk..l..d:".k.Q.lk_l .k.Q.v_" / like label lk..e..d:"~_n~.k.Q.v_" / like editable? lk..f:-20$ / max 20 lk..t:"oper2[O 6;lk]" / -> evaluate like o:"or[]";o..l:"|" / or-clause button r:":[1=#L;reset_all;reset][]";r..l:`Reset / reset current disjunct ra:"reset_all[]";ra..l:`"Reset All" / reset all disjuncts o..c:r..c:ra..c:`button / buttons \d ^ Q..c:`form / outer level is a form Q..a:(`P`S;`R;Q.B,`lk`o`r`ra) / arrangement Q..l:`Query / label Q..kl:".k.menu[]" / general help Q..x:90 / width Q..y:40 / height menu:{{if[~_n~x;Z[x][]]}@`4:`menu,,!Z} / main menu open:{z;if[-3=4:f:`4:(`open;x;"open ",$y);y@0:f]} / open save:{z;if[-3=4:f:`4:(`save;x;"save ",$y);f 0:y[]]} / save Z.help:{`show$`.k.Help} / display help Z.open_query:open[`txt;`query] / open query Z.save_query:save[`txt;`query] / save query Help..l:`Help / label Help..e:0 / no edit Help..x:100 / width Help:("Query table" / help text "===========" "" "Projection" "----------" "Double-click on an unprojected field to project it." "Double-click on a projected field to deproject it." "A data-field can be projected more than once." "Ungrouped key-fields are blue, grouped key-fields are green." "Single-click on a projected field to select it." "If a key-field is selected, then 'Group' chooses it for grouping." "If a data-field is selected, then 'Group' cycles through aggregation functions." "'Up' and 'Down' move projected fields." "'Reset' clears current projection." "" "Restriction" "-----------" "Selected values are white, unselected values are gray." "Double-click on selected value v in field f to create the relation f=v." "Double-click on unselected v to generate a new 'Or' clause containing f=v." "Single-click on value v in field f to select (f;v)." "Click on the label of field f to select f." "If (f;v) is selected, push a relation button o (=, <, >, ~-, ~<, ~>) to create (f o v)" "If (f;v) or f is selected, enter a pattern in 'like' to match f values" "'|' appends an 'Or' clause." "'Reset' clears the current 'Or' clause." "'Reset All' clears the 'Where' clause." "" "Index table view" "----------------" "Click on the label of a field to sort the view on that field." "Single-click on a row to stack the fields for that row." "" "Value table view" "----------------" "Click on the label of the view to transpose rows and columns." "Enter 'From' and/or 'To' dates to restrict date-range (dd/mm/yyyy)." "" "Queries" "-------" "A Pseudo-SQL query is built up dynamically in the course of operation." "From the menu, choose \"Save Query\" to save the current query as a .txt file." "From the menu, choose \"Open Query\" to open and evaluate a saved query." "Click on a line in an 'Or' clause to make that the current clause.") if[2=#_i;tables ._i]