DSXchange

Table -1
CID,CNAME,STATE,STATUS
11,sun,AP,1001
22,moon,TN,1111
33.star,kk,1000
44,pluto,JK,1011
55,sunn,ap,1000
66,rad,AP,1111
73.star,kk,1000
84,pluto,JK,1011
95,sunn,ap,1000
46,rad,AP,1111

Table-2
state,State_desc
AP,ANDHRA PRADESH
TN,TAMIL NADU
KK,KANYA KUMARI
DL,DELHI

Required output
CID,CNAME,STATE,State_desc
11,suman,AP,ANDHRA PRADESH
22,moon,TN,TAMIL NADU
33.star,kk,KANYA KUMARI
44,pluto,JK,NOT APPLICABLE
55,sunn,ap,NOT APPLICABLE
66,rad,AP,ANDHRA PRADESH
73.star,kk,KANYA KUMARI
84,pluto,JK,NOT APPLICABLE
95,sunn,ap,NOT APPLICABLE
46,rad,AP,ANDHRA PRADESH

HOW TO DO THIS ONE??

Hi Ravi you can use a look up stage to do the above job .Perform look up on 'state' column,you get the desired output

Make sure you "upcase" the incoming data and handle lookup failures as well, i.e. your "NOT APPLICABLE" results.