Adding Columns in kdb based on other columns value

Question

I have a column like this in dataframe named test:

Name    Client
 A       P
 B       Q
 C       R
 D       S
 E       T

I need to to create a new column clienttype in the same dataframe with condition that, if Client = P or Q clienttype = first. If client type = R or S clienttype = second and so on. Can anyone tell how to do this in kdb? Thanks

Answer 1

Could this be solved with a dictionary?

q)update ClientType:(`P`Q`R`S!`first`first`second`second)Client from tab
Name Client ClientType
----------------------
A    P      first
B    Q      first
C    R      second
D    S      second
E    T

Extension to this: You can also use vector conditionals ?[;;] for these types of problems. Unfortunately in this case in would result in many nested vector conditionals:

update Clienttype:?[Client in`P`Q;`first;?[Client in`R`S;`second;`third]]from tab
Name Client Clienttype
----------------------
A    P      first
B    Q      first
C    R      second
D    S      second
E    T      third

If you only had two possible options (e.g. first and second) this approach could prove useful.

Answer 2

You did not tell us the types of the columns in your table, but if all your entries are single letter, they are best represented by q char type:

q)show t:([]Name:"ABCDE";Client:"PQRST")
Name Client
-----------
A    P
B    Q
C    R
D    S
E    T

Now, the Clienttype is best stored as a number which can be computed as

q)update Clienttype:1+(("i"$Client)-"i"$"P")div 2 from t
Name Client Clienttype
----------------------
A    P      1
B    Q      1
C    R      2
D    S      2
E    T      3

or if you must use symbols,

q)types:`first`second`third`fourth
q)update Clienttype:types(("i"$Client)-"i"$"P")div 2 from t
Name Client Clienttype
----------------------
A    P      first
B    Q      first
C    R      second
D    S      second
E    T      third