Hi,
I have a table (mytable) with follwoing sample data:
date sym var
I would like to have a “case” statement as in Oracle SQL that
The results is as follows:
date sym var flag_1 flag_2 flag_3
I was able to get this far:
select date, sym, var,
Thanks in advance.
Ning
Hi Ning,
Try this:
q)t:(
q)f1:{[row]d:row[date];v:row[VAR];$[d>=2009.09.10;signum v;0]}
q)f2:{[row]d:row[date];v:row[VAR];$[d>=2009.09.15;signum v;0]}
q)f3:{[row]d:row[date];v:row[VAR];$[d>=2009.09.20;signum v;0]}
Nathan
2009/10/6 Ninja Li <nickli2000@gmail.com >
Hi,
? I have a table (mytable) with follwoing sample data:
? ?date ? ? ? ? ?sym ? ? ? var
? I would like to have a “case” statement as in Oracle SQL that
The results is as follows:
? ?date ? ? ? ? ?sym ? ? ? var ? ? flag_1 ? ? flag_2 ? ? flag_3
? I was able to get this far:
?select date, sym, var,
?Thanks in advance.
?Ning
Attila
October 6, 2009, 8:34pm
3
update flag_1:?[date>2009.09.10;signum VAR;0]from t
2009.09.08 XYC 0.049 0 0 0
Revgards,
Thanks a lot for your help. It works well.On Oct 6, 3:34?pm, Attila Vrabecz <attila.vrab…> wrote:> update flag_1:?[date>2009.09.10;signum VAR;0]from t> or> update flag_1:signum[VAR]*date>2009.09.10 from t> etc, the latter one is a bit faster>> q)![t;();0b;flag_1flag_2flag_3!{(*;(signum;VAR);(>;date;x))}each ?> 2009.09.10+0 5 10]> date ? ? ? sym VAR ? flag_1 flag_2 flag_3> -----------------------------------------> 2009.09.08 XYC 0.049 0 ? ? ?0 ? ? ?0> 2009.09.09 XYC -0.02 0 ? ? ?0 ? ? ?0> 2009.09.15 XYC 0.024 1 ? ? ?0 ? ? ?0> 2009.09.16 XYC -0.01 -1 ? ? -1 ? ? 0> 2009.09.22 XYC 0.063 1 ? ? ?1 ? ? ?1> 2009.09.23 XYC -0.01 -1 ? ? -1 ? ? -1>> Revgards,> ? ?Attila>> On 6 Oct 2009, at 20:15, Nathan Perrem wrote:>>>> > Hi Ning,>> > Try this:>> > q)t:([]date:(2009.09.08 2009.09.09 2009.09.15 2009.09.16 2009.09.22 ?> > 2009.09.23);sym:6#XYC;VAR:(0.049 -0.02 0.024 -0.01 0.063 -0.01))>> > q)f1:{[row]d:row[date];v:row[VAR];$[d>=2009.09.10;signum v;0]}> > q)update flag_1:(f1 each t) from t>> > q)f2:{[row]d:row[date];v:row[`VAR];$[d>=2009.09.15;signum v;0]}> > q)update flag_2:(f2 each t) from `t>> > q)f3:{[row]d:row[`date];v:row[`VAR];$[d>=2009.09.20;signum v;0]}> > q)update flag_3:(f3 each t) from `t>> > Nathan>> > 2009/10/6 Ninja Li <nickli2…>>> > Hi,>> > ? I have a table (mytable) with follwoing sample data:>> > ? ?date ? ? ? ? ?sym ? ? ? var> > 2009.09.08 ? ?XYC ? ? 0.049> > 2009.09.09 ? ?XYC ? ? -0.02> > 2009.09.15 ? ?XYC ? ? 0.024> > 2009.09.16 ? ?XYC ? ? -0.01> > 2009.09.22 ? ?XYC ? ? 0.063> > 2009.09.23 ? ?XYC ? ? -0.01>> > ? I would like to have a “case” statement as in Oracle SQL that> > returns flags. The conditions will be:> > case when date >= 2009.09.10 then sign(var) else 0 ?flag_1;> > case when date >= 2009.09.15 then sign(var) ?else 0 flag_2;> > case when date >= 2009.09.20 then sign(var) ? else 0 flag_3;> > ?(sign: 1 for positive value, 0 for 0 value, -1 for negative value)>> > The results is as follows:>> > ? ?date ? ? ? ? ?sym ? ? ? var ? ? flag_1 ? ? flag_2 ? ? flag_3> > 2009.09.08 ? ?XYC ? ? 0.049 ? ? ?0 ? ? ? ? ? ? 0 ? ? ? ? ? 0> > 2009.09.09 ? ?XYC ? ? -0.02 ? ? ? 0 ? ? ? ? ? ? 0 ? ? ? ? ? 0> > 2009.09.15 ? ?XYC ? ? 0.024 ? ? ?1 ? ? ? ? ? ? 1 ? ? ? ? ? 1> > 2009.09.16 ? ?XYC ? ? -0.01 ? ? ?-1 ? ? ? ? ? ?-1 ? ? ? ? ? 0> > 2009.09.22 ? ?XYC ? ? 0.063 ? ? ?1 ? ? ? ? ? ? 1 ? ? ? ? ? 1> > 2009.09.23 ? ?XYC ? ? -0.01 ? ? ?-1 ? ? ? ? ? ? -1 ? ? ? ? -1>> > ? I was able to get this far:>> > ?select date, sym, var,> > ? ? ? ? flag_1:{$[x>=2009.09.10;1;0]}‘[date],> > ? ? ? ? flag_2:{$[x>=2009.09.15;1;0]}’[date],> > ? ? ? ? flag_3:{$[x>=2009.09.20;1;0]}'[date]> > ?from ? mytable;>> > ?Thanks in advance.>> > ?Ning- Hide quoted text ->> - Show quoted text -</nickli2…></attila.vrab…>