Right Crosstab Anyone?

q)a:(c1:xxxxxxyyyy;c2:abaab;c3:10 20 30 40 50)

q)a
c1 c2 c3

xx a  10
xx b  20
xx a  30
yy a  40
yy b  50

q)rct:exec sum c3 by c1,c2 from a

q)rct

c1 c2   
xx a 40
xx b 20
yy a 40
yy b 50

q)select from rct
'type
  [0]  select from rct
       ^

q)exec from rct
'type
  [0]  exec from rct
       ^

q)exec xxa from rct
'type
  [0]  exec xxa from rct
       ^

q)exec from rct where c1=xx 'type   [0]  exec from rct where c1=xx
       ^

***** Seems everything gives us a `type error, why?!?!

***** Although indexing into the “dictionary” works.

q)rct xxa
40

Any ideas on this one folks???

You can choose between:
rct:exec c3:sum c3 by c1,c2 from a  

and

rct:select sum c3 by c1,c2 from a  

  Konstantin

Hi,

It’s because rct is a dictionary whose key is a table and whose value is a list.

q)rct:exec sum c3 by c1,c2 from a

q)type@/:(key;value)@:rct

98 7h

Reassign the value so that both the key and value of rct are tables, thereby making rct a keyed table and your statements will work. 

q)rct:exec sc3:sum c3 by c1,c2 from a

q)type@/:(key;value)@:rct

98 98h

q)

q)select from rct

c1 c2| sc3

-----| —

xx a | 40

xx b | 20

yy a | 40

yy b | 50

Jason

It seems like the select statement requires the table columns to have headers. For example,

q)rct:exec sumc3:sum c3 by c1,c2 from a

q)rct

c1 c2| sumc3

-----| -----

xx a | 40

xx b | 20

yy a | 40

yy b | 50

q)select from rct

c1 c2| sumc3

-----| -----

xx a | 40

xx b | 20

yy a | 40

yy b | 50

Hi,

Yes I understand that I can create a keyed table, but I’m trying to understand why the type error and what sorts of queries will work on a rct. select works OK on a dictionary, regular crosstab (blank column on left), and keyed table.  In constructing dictionaries where the arguments can be either a list or a conformable table there are four combinations: LL, LT, TL, TT.  I’m getting a `type exception with the rct…does anyone have a select or exec statement that works w/o changing the dictionary?  Thanks!

exec is really a general form of select and can produce tables, dictionaries and lists. 

When you don’t supply a column name, the resulting aggregation will produce a dictionary, note that the aggregation clause is specifying just an aggregation 

q)parse "exec sum c3 by c1,c2 from a"?a()c1c2!c1c2,(sum;c3)

When a column name is supplied, the result is promoted into a table. The aggregation clause here is a dictionary

q)parse "exec sumc3:sum c3 by c1,c2 from a"?a()c1c2!c1c2(,sumc3)!,(sum;c3)`

You can use .Q.qt to determine if the result is a table or keyed tab

On Tuesday, March 5, 2019 at 10:40:45 AM UTC-5, Erik Friis wrote:

q)a:(c1:xxxxxxyyyy;c2:abaab;c3:10 20 30 40 50)

q)a
c1 c2 c3

xx a  10
xx b  20
xx a  30
yy a  40
yy b  50

q)rct:exec sum c3 by c1,c2 from a

q)rct

c1 c2   
xx a 40
xx b 20
yy a 40
yy b 50

q)select from rct
'type
  [0]  select from rct
       ^

q)exec from rct
'type
  [0]  exec from rct
       ^

q)exec xxa from rct
'type
  [0]  exec xxa from rct
       ^

q)exec from rct where c1=xx 'type   [0]  exec from rct where c1=xx
       ^

***** Seems everything gives us a `type error, why?!?!

***** Although indexing into the “dictionary” works.

q)rct xxa
40

Any ideas on this one folks???

Regular crosstab seems to work fine.

q)ct:exec c2,sum c3 by c1 from a

q)ct

  c2     c3
xx ab`a 60
yy ab   90

q)select xx from ct

  c2    c3
xx a b a 60

q)exec xx from ct
c2| ab`a
c3| 60

Thanks Callum.  I understand that I can create a table with the exec template by naming the aggregate field but my question is why the type exception with the rct?  And is there any way with the rct the way it is to get an answer rather than a type exception with an exec or select query?

Because rct isn’t a table, it’s a dictionary. In your latter example you still haven’t produced a table, .Q.qt is false, and if you try to select a column from that table it will give you a type error again 

In regards to tables, the following qsql query makes no sense

q)select xx from ct

  c2    c3
xx a b a 60 

xx is not a column, which the select statement should have extracted, instead, you got a row from your dictionary.  

In fact for both ct and rct if you examine with -3! you can see that they are not tables

q)-3!rct

“(s#+c1c2!(p#xxxxyyyy;abab))!40 20 40 50”

q)select from rct

'type

[0] select from rct

^

q)-3!ct

s#xxyy!+c2c3!((aba;ab);60 90)”


q)tct:exec sumc3:sum c3 by c1,c2 from a

q)-3!tct

“(s#+c1c2!(p#xxxxyyyy;abab))!+(,`sumc3)!,40 20 40 50”

Callum Biggs | Data Scientist | Kx | +44 75 7627 4469 | cbiggs@kx.com


Kx
is a division of First Derivatives

A keyed table isn’t really a table either, it’s a dictionary too.  The fact is that the select and exec templates work on dictionaries as well and keys are allowed in the select clause.  When applied to a dictionary (the various types) the by clause is disallowed.

q)d
finches    | 27
nuthatches | 42
sparrows   | 50
woodpeckers| 153

q)select sparrows from d
sparrows| 50

q)select finches,nuthatches from d
finches   | 27
nuthatches| 42

q)ct
           | counter  sightings
-------    | ------------------
finches    | ab     27      
nuthatches | abcd 42      
sparrows   | abc   50       woodpeckers| abc   153     

q)select finches,nuthatches from ct
          | counter  sightings
-------   | ------------------
finches   | ab     27      
nuthatches| abcd 42      

Oddly this seemingly formally correct syntax doesn’t work on a proper keyed table which *is* a dictionary:

q)kt2

family     counter  sightings
finches    ab     27      
nuthatches abcd 42      
sparrows   ab`c   50      
woodpeckers ab`c   153     

q)exec finches from kt2
'finches
  [0]  exec finches from kt2
       ^
q)exec finches from kt2 finches

q)select from kt2 where family=`finches

family counter sightings
finches a b     27      

Of course we still have the enigma of the rct (right crosstab) and the 'type exception. Is anyone able to write a select or exec template that works on that one? :)

A keyed table is a dictionary where the key and values are both tables, not just any dictionary. In your case, you have two different cases. In one the key is a list, in the other, the values are a list. 

Your latter examples are demonstrating expected behaviour from https://code.kx.com/q/ref/qsql/#select. I’m unsure as to why you are expecting to extract rows with your column specification. In your case, it appears to just be indexing into the dictionary structure you have provided. To the best of my knowledge, exec and select for dictionaries is undocumented, so it’s not a surprise that the results are unpredictable. 

Regards,

Callum

Callum Biggs | Data Scientist | Kx | +44 75 7627 4469 | cbiggs@kx.com


Kx
is a division of First Derivatives