So basically the object “t” from your previous example (the result of JSON parsing by .j.k) is a dictionary where the “values” are dictionaries - and remember, in KDB a list of dictionaries is a table:
q)value t
quote stats
marketCappeRatio!8.938324e+11 19.78 (,EBITDA)!,7.6569e+10 marketCappeRatio!7.448348e+11 35.89 (,EBITDA)!,3.2714e+10
marketCappeRatio!5.762372e+11 303.51 (,EBITDA)!,1.4021e+10 marketCappeRatio!5.318502e+11 35.27 (,EBITDA)!,2.0304e+10
So this is what the “exec” statement in my example is operating on. exec extracts columns from a table - if you exec a single column, the return will be a list. In my previous example, I use join-each (,') to combine the two dictionary columns and exec them as one - therefore the return is a list. As these columns are dictionary columns, when we exec this list, we get a list of dictionaries, i.e. a table
q)exec (quote,'stats) from value t
marketCap peRatio EBITDA
8.938324e+11 19.78 7.6569e+10
7.448348e+11 35.89 3.2714e+10
5.762372e+11 303.51 1.4021e+10
5.318502e+11 35.27 2.0304e+10
>From here it’s fairly trivial to use join-each to combine this with a table containing the sym values, and xcol is used finally to rename the columns as you requested
Hope that helps
Jonathon
From: personal-kdbplus@googlegroups.com <personal-kdbplus@googlegroups.com> on behalf of ag <anuj.goyal@gmail.com>
Sent: 27 November 2017 17:19
To: Kdb+ Personal Developers
Subject: Re: [personal kdb+] Re: q/kdb+ api for getting market and financial data from IEX
Amazing! This is great. I am reading through the exec documentation but there isn’t a full description, just examples.
It seems to pull out the contents from the dictionary key - is that correct?
http://code.kx.com/q/ref/qsql/#exec
q)foo: flipab`c!flip(0 0 0;1 2 3;2 4 6)
q)foo
a b c
-----
0 0 0
1 2 3
2 4 6
q)exec a from value foo
'type
[0] exec a from value foo
^
q)exec a from foo
0 1 2
( sym:key t),'exec quote from value t
On Monday, November 27, 2017 at 3:40:38 AM UTC-8, Jonathon McMurray wrote:
Here?s one way to do it:
q)symmcappeebitda xcol ( sym:key t),'exec (quote,'stats) from value t
sym mcap pe ebitda
------------------------------------
AAPL 8.983506e+11 19.88 7.6569e+10
GOOGL 7.340723e+11 35.37 3.2714e+10
AMZN 5.715004e+11 301.02 1.4021e+10
FB 5.311237e+11 35.22 2.0304e+10
Hope this helps
Jonathon
From: personal…@googlegroups.com [mailto:personal…@googlegroups.com] On Behalf Of ag
Sent: 27 November 2017 08:14
To: Kdb+ Personal Developers <personal…@googlegroups.com>
Subject: [personal kdb+] Re: q/kdb+ api for getting market and financial data from IEX
What is the best way to “unnest” t?
I would like a 4 column table with cols: symmcappeebitda
q)t:.j.k .Q.hg`$“https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,googl,amzn,fb&types=quote,stats&filter=marketCap,peRatio,EBITDA”
q)
q)t
| quote stats
---- | -------------------------------------------------------------
AAPL | marketCappeRatio!8.983506e+11 19.88 (,`EBITDA)!,7.6569e+10
GOOGL| marketCappeRatio!7.340723e+11 35.37 (,`EBITDA)!,3.2714e+10
AMZN | marketCappeRatio!5.715004e+11 301.02 (,`EBITDA)!,1.4021e+10
FB | marketCappeRatio!5.311237e+11 35.22 (,`EBITDA)!,2.0304e+10
q)
q)type t
99h
–
Submitted via Google Groups