cancel
Showing results for 
Search instead for 
Did you mean: 

apply formula to all value in a table

glorieuxh
New Contributor
Hi there,

I have a table (type 98) which shows dividends estimates by date and ticker. 
Column 1  shows all the tickers. column 2, all the spots, and then another 100 columns with the different dates. See below.

mnemo    spot    2020.06.30    2020.07.22   ...
aapl UQ   100     0.52               ...
...


I am struggling with two things. first I am trying to divide all values by the stock spot. Second I am trying to group the columns by year.
Could anyone please help me on this?

Thanks a lot in advance,
Victor


2 REPLIES 2

rathore_ajay
New Contributor
Seems like your table is pivoted, you will have to unpivot it and then do the grouping

q)t
mnemo spot     2020.06.30 2020.07.01
------------------------------------
appl  2.782122 0.391543   0.5919004 
hsbc  2.392341 0.08123546 0.8481567 
vod   1.508133 0.9367503  0.389056  
q)
q)t:ungroup(`mnemo`spot#t),' flip`date`val!(count[t]#enlist"D"$string d;flip t d:2_cols t)
q)t
mnemo spot     date       val       
------------------------------------
appl  2.782122 2020.06.30 0.391543  
appl  2.782122 2020.07.01 0.5919004 
hsbc  2.392341 2020.06.30 0.08123546
hsbc  2.392341 2020.07.01 0.8481567 
vod   1.508133 2020.06.30 0.9367503 
vod   1.508133 2020.07.01 0.389056  
q)
q)select val%spot by date, mnemo from t
date       mnemo| val       
----------------| ----------
2020.06.30 appl | 0.1407354 
2020.06.30 hsbc | 0.03395647
2020.06.30 vod  | 0.6211324 
2020.07.01 appl | 0.2127515 
2020.07.01 hsbc | 0.35453   
2020.07.01 vod  | 0.2579719 


-Ajay

alvi_kabir919
New Contributor
without ungroup (~30% faster but messier code):

q)t:flip `mnemo`spot`2020.06.30`2020.07.22`2020.08.03`2020.08.04`2020.08.05!(`aapl`ibm`msft`tsla;100 200 150 600;.52 1.2 2.4 .2;.76 1.5 2.1 .3;.6 1.3 1.9 .14;.65 1.23 1.89 .5;.55 1.1 2.01 .34)
q)c:2_cols t
q)nt:exec flip ((`mnemo`spot!(raze count[c]#'mnemo;raze count[c]#'spot)),`date`divd!("D"$string (count[t[c]0]*count c)#c;raze flip t c)) from t
q)nt
mnemo spot date       divd
--------------------------
aapl  100  2020.06.30 0.52
aapl  100  2020.07.22 0.76
aapl  100  2020.08.03 0.6
aapl  100  2020.08.04 0.65
aapl  100  2020.08.05 0.55
ibm   200  2020.06.30 1.2
ibm   200  2020.07.22 1.5
ibm   200  2020.08.03 1.3
ibm   200  2020.08.04 1.23
ibm   200  2020.08.05 1.1
msft  150  2020.06.30 2.4
msft  150  2020.07.22 2.1
msft  150  2020.08.03 1.9
msft  150  2020.08.04 1.89
msft  150  2020.08.05 2.01
tsla  600  2020.06.30 0.2
tsla  600  2020.07.22 0.3
tsla  600  2020.08.03 0.14
tsla  600  2020.08.04 0.5
tsla  600  2020.08.05 0.34
q)select divd%spot by date,mnemo from nt
date       mnemo| divd
----------------| ------------
2020.06.30 aapl | 0.0052
2020.06.30 ibm  | 0.006
2020.06.30 msft | 0.016
2020.06.30 tsla | 0.0003333333
2020.07.22 aapl | 0.0076
2020.07.22 ibm  | 0.0075
2020.07.22 msft | 0.014
2020.07.22 tsla | 0.0005
2020.08.03 aapl | 0.006
2020.08.03 ibm  | 0.0065
2020.08.03 msft | 0.01266667
2020.08.03 tsla | 0.0002333333
2020.08.04 aapl | 0.0065
2020.08.04 ibm  | 0.00615
2020.08.04 msft | 0.0126
2020.08.04 tsla | 0.0008333333
2020.08.05 aapl | 0.0055
2020.08.05 ibm  | 0.0055
2020.08.05 msft | 0.0134
2020.08.05 tsla | 0.0005666667

q)\t do[10000;exec flip ((`mnemo`spot!(raze count[c]#'mnemo;raze count[c]#'spot)),`date`divd!("D"$string (count[t[c]0]*count c)#c;raze flip t c)) from t]
96