function to convert `B or `M to numerical value

/ value (dropping last char) * remove last letter, if M then 1e6, otherwise 1e9q)f:{[x] ("F"$-1_x)*(?["M"=-1#x;1e6;1e9]@0)} q)f["5.3M"]5300000fq)f["10.35B"]1.035e+10/what do I need to do to change it to work with a function?q)t:([]s:ab; m:5.3M10.35B)q)ts m --------a 5.3M b 10.35Bq)select f m from t'type [1] f:{[x] ("F"$-1_x)*(?["M"=-1#x;1e6;1e9]@0)} ^q)select f[m] from t'type [4] f:{[x] ("F"$-1_x)*(?["M"=-1#x;1e6;1e9]@0)} ^q)a:("7B";"5M")q)f:{[x] ("F"$-1_/:x)*(?[raze "M"=-1#/:x;1e6;1e9])} / this function seems to work on listsq)f[a]7e+09 5000000q)select f m from t'type [1] f:{[x] ("F"$-1_/:x)*(?[raze "M"=-1#/:x;1e6;1e9])} ^

  1. Is there a better/faster way to write f?

  2. when using f in select, should  f  be written assuming a vector of string values will be passed in?

Content-ID: <3F6F93AF2FCE944A9FA0DD6ED6F5D95D@GBRP123.PROD.OUTLOOK.COM>

The issue is the difference in types between the m column in your table (symbol) and the variables your passing in when testing (string).

Can either update your m column to string;

q)f:{x*(?[“M”=-1#x;1e6;1e9]@0)}

q)t:(s:ab; m:5.3M10.35B)

q)select f’[string m] from t

m        


5300000  

1.035e+10

Or change your function to update to string;

q)f:{(“F”$-1_string x)*(?[“M”=-1#string x;1e6;1e9]@0)} 

q)select f’[m] from t

m        


5300000  

1.035e+10

Thanks,

Caolan

Hey, think there was something similar discussed here a few months ago, except with just “B” vs no “B” as a possible ending. If you know that the endings must be only either “B” or “M”, that helps. Building from that previous discussion, this is one possibility that should improve your performance - still need to convert m to string first, would be better if it started out as a string instead of symbol though, e.g. if you’re reading in from a csv use (…“*”…;enlist “,”) instead of (…“S”…;enlist “,”) 

v:{(1e6 1e9 “B”=last each x)*“F”$-1 _'x}

select v string m from t

However, I would first try and change whatever is giving you this data from upstream to give you normal numbers instead of “B” and “M” endings - although maybe that’s not possible. 

Cheers

Ryan

x:("30B";"5M")1e6 1e9 "B"=last each x1e6 1e9 10b / can you explain how this construct works? it seems like another way to construct a simple if statement

The numbers are being taken from Yahoo’s API so unfortunately I can’t change them to be normal numbers.

> it seems like another way to construct a simple if statement

exactly!  the bool list indexes a binary choice: 1e6 1e9.

just like ab 1 0 results in ba

or ab 1011b is babb.