Below 100,000 & Above 100,000

[sagaraperera]

Dear All

I try to get a feild of below 100,000 & above 100,000 GROSSTOT amount but it is not working properly. Please give me a solution. my script given below. I use  this   IF(GROSSTOT<=100000,'Below_100000',IF(GROSSTOT>100001,'OVER_100000',0))AS AGE, but not working properly.

LOAD BCOD,

    CLA_CODE,

    PRD_CODE,

    POLICY_NO,

    REF_NO,

    DATE(floor(MARKETING_DATE)) as  MARKETING_DATE,

    MDATE,

    YEAR,

    MONTH, 

    NAME,

    ME_CODE,

    DATE(floor(TRN_DATE)) as  TRN_DATE,

    TRAN_YEAR,

    TRAN_MONTH,

    POL_PERIOD_FROM,

    SCCOD,

    IF(SCCOD>='SC0','COUNTER')AS CITY_COUNTER,

    CP,

    RS,

    TC,

    GROSSTOT,

    TYPE,

    N_R_NOS1,

    F_NOS2

thanks

sagara

[gautik92]

if(GROSSTOT<=100000,'Below','Above')

[rubenmarin]

Hi Sanka, It should work, only hole is between 100000 and 100001, wich will return '0', maybe this works better:

IF(GROSSTOT<=100000,'Below_100000',IF(GROSSTOT>100000,'OVER_100000'))AS AGE

yep, gowtham expression was better

IF(GROSSTOT<=100000,'Below_100000', 'OVER_100000')AS AGE

Maybe your problem comes when you try to use the AGE field, where and how are you using this field?

[MarcoWedel]

Hi,

please don't post the same questions multiple times and try to close your threads if you received correct answers.

Below 100,000 &amp; Above 100,000

below above

Below &amp; above

thanks

regards

Marco