below above

[sagaraperera]

Dear All

I try to get a two type of field it is  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 amount coming to mix 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

[MarcoWedel]

Hi,

one solution to keep the details and add the overall AGE field at the same time could be:

table1:
LOAD * FROM [https://community.qlik.com/thread/186101] (html, codepage is 1252, embedded labels, table is @1) Where REGION <> 'Total';
LOAD * FROM [https://community.qlik.com/thread/186101] (html, codepage is 1252, embedded labels, table is @2) Where REGION <> 'Total';

Left Join (table1)
LOAD REGION, BCOD, SCCOD,
    If(Sum(GROSSTOT)>=100000,'Over_100000','Below_100000') as AGE
Resident table1
Group By REGION, BCOD, SCCOD;

hope this helps

regards

Marco