Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Can you post the sample lines which is giving the mixed result?
below 100,000
REGION | BCOD | SCCOD | GROSSTOT |
FRRG | CO06 | SC007 | 135,084 |
FRRG | CO06 | SC025 | 106,610 |
FRRG | CO06 | SC028 | 218,449 |
FRRG | CO06 | SC034 | 11,630 |
FRRG | CO08 | SC015 | 9,136 |
FRRG | CO08 | SC022 | 87,612 |
FRRG | CO09 | SC004 | 76,824 |
FRRG | CO10 | SC023 | -2,742 |
FRRG | CO11 | SC014 | -206,513 |
FRRG | CO11 | SC042 | -50,632 |
FRRG | CO25 | SC002 | 173,466 |
FRRG | CO25 | SC046 | 30,875 |
MCRG | CO19 | SC024 | 181,179 |
MCRG | CO19 | SC027 | 305,096 |
MCRG | CO20 | SC033 | 102,545 |
MCRG | CO20 | SC043 | 119,490 |
MCRG | CO21 | SC011 | 162,159 |
MCRG | CO22 | SC019 | 42,859 |
MCRG | CO30 | SC021 | 153,930 |
MCRG | CO32 | SC017 | 74,888 |
MCRG | CO32 | SC029 | 6,059 |
MCRG | CO35 | SC026 | -48,169 |
MCRG | CO35 | SC031 | -237,657 |
MCRG | CO39 | SC040 | -20,440 |
MCRG | CO40 | SC037 | 225,066 |
MKRG | CO03 | SC008 | 1,092,100 |
MKRG | CO23 | SC030 | 100,224 |
MKRG | CO24 | SC010 | 19,765 |
MKRG | CO41 | SC016 | 52,215 |
MKRG | CO43 | SC038 | 5,480 |
MRRG | CO26 | SC039 | 138,563 |
MRRG | CO27 | SC003 | 33,830 |
MRRG | CO27 | SC032 | 92,677 |
MRRG | CO27 | SC041 | 113,403 |
MRRG | CO27 | SC044 | 171,460 |
MRRG | CO27 | SC048 | 75,499 |
MSRG | CO12 | SC018 | 57,300 |
MSRG | CO12 | SC036 | 30,811 |
MSRG | CO12 | SC050 | 2,332 |
MSRG | CO13 | SC035 | 61,139 |
MSRG | CO13 | SC047 | 113,798 |
SSRG | CO15 | SC006 | 214,051 |
SSRG | CO16 | SC012 | 51,303 |
SSRG | CO17 | SC005 | 78,343 |
SSRG | CO17 | SC009 | 8,931 |
SSRG | CO18 | SC020 | 2,264 |
SSRG | CO18 | SC045 | 153,456 |
Total | 4,325,750 |
Above 100,000
REGION | BCOD | SCCOD | GROSSTOT |
FRRG | CO08 | SC015 | 195,038 |
MCRG | CO19 | SC027 | 342,694 |
MCRG | CO22 | SC019 | 159,519 |
MCRG | CO32 | SC017 | 120,158 |
MCRG | CO32 | SC029 | 148,662 |
MCRG | CO35 | SC031 | 429,387 |
MCRG | CO39 | SC049 | 128,215 |
MCRG | CO40 | SC037 | 210,830 |
MKRG | CO23 | SC030 | 170,500 |
MKRG | CO24 | SC010 | 290,005 |
MRRG | CO27 | SC041 | 111,330 |
MSRG | CO13 | SC035 | 108,605 |
Total | 2,414,942 |
thanks
Hi SANKA,
It is giving me the correct result..I have used
IF(GROSSTOT<=100000,'Below_100000',IF(GROSSTOT>100001,'OVER_100000',0))AS AGE
Just to make sure the GROSSTOT is number..
is it possible to post to sample qvw?
Mr.Settu
This expression OK, but my GROSSTOT include below 100,000 and 100,000 amount of sales. therefore my table come to total amount. then age come to incorrect. can you include Sum(GROSSTOT ) in that expression . please refer my table
REGION | BCOD | SCCOD | AGE | All_Product |
FRRG | CO06 | SC007 | Below_100000 | 135,084 |
FRRG | CO06 | SC025 | Below_100000 | 106,610 |
FRRG | CO06 | SC028 | Below_100000 | 218,449 |
FRRG | CO06 | SC034 | Below_100000 | 11,630 |
FRRG | CO08 | SC015 | Below_100000 | 9,136 |
FRRG | CO08 | SC015 | OVER_100000 | 195,038 |
MCRG | CO32 | SC029 | OVER_100000 | 148,662 |
MCRG | CO35 | SC026 | Below_100000 | -48,169 |
MCRG | CO35 | SC031 | Below_100000 | -237,657 |
MCRG | CO35 | SC031 | OVER_100000 | 429,387 |
MCRG | CO39 | SC040 | Below_100000 | -20,440 |
MCRG | CO39 | SC049 | OVER_100000 | 128,215 |
MCRG | CO40 | SC037 | Below_100000 | 225,066 |
MCRG | CO40 | SC037 | OVER_100000 | 210,830 |
MKRG | CO03 | SC008 | Below_100000 | 1,092,100 |
MKRG | CO23 | SC030 | Below_100000 | 100,224 |
MKRG | CO23 | SC030 | OVER_100000 | 170,500 |
MKRG | CO24 | SC010 | Below_100000 | 19,765 |
MKRG | CO24 | SC010 | OVER_100000 | 290,005 |
MKRG | CO41 | SC016 | Below_100000 | 52,215 |
MKRG | CO43 | SC038 | Below_100000 | 5,480 |
Hi SANKA,
I think you need to do the Group by and Sum in the scrip like below..
Load REGION,BCOD,SCCOD,
Sum(GROSSTOT) as GROSSTOT,
if(Sum(GROSSTOT)<=100000,'Below_10000',
if(Sum(GROSSTOT)>100001,'OVER_100000',0)) as AGE Group by REGION,BCOD,SCCOD;
LOAD * INLINE [
REGION, BCOD, SCCOD, GROSSTOT
FRRG, CO06, SC007, "135,084"
FRRG, CO06, SC025, "106,610"
FRRG, CO06, SC028, "218,449"
FRRG, CO06, SC034, "11,630"
FRRG, CO08, SC015, "9,136"
FRRG, CO08, SC022, "87,612"
FRRG, CO09, SC004, "76,824"
FRRG, CO10, SC023, "-2,742"
FRRG, CO11, SC014, "-206,513"
FRRG, CO11, SC042, "-50,632"
FRRG, CO25, SC002, "173,466"
FRRG, CO25, SC046, "30,875"
MCRG, CO19, SC024, "181,179"
MCRG, CO19, SC027, "305,096"
MCRG, CO20, SC033, "102,545"
MCRG, CO20, SC043, "119,490"
MCRG, CO21, SC011, "162,159"
MCRG, CO22, SC019, "42,859"
MCRG, CO30, SC021, "153,930"
MCRG, CO32, SC017, "74,888"
MCRG, CO32, SC029, "6,059"
MCRG, CO35, SC026, "-48,169"
MCRG, CO35, SC031, "-237,657"
MCRG, CO39, SC040, "-20,440"
MCRG, CO40, SC037, "225,066"
MKRG, CO03, SC008, "1,092,100"
MKRG, CO23, SC030, "100,224"
MKRG, CO24, SC010, "19,765"
MKRG, CO41, SC016, "52,215"
MKRG, CO43, SC038, "5,480"
MRRG, CO26, SC039, "138,563"
MRRG, CO27, SC003, "33,830"
MRRG, CO27, SC032, "92,677"
MRRG, CO27, SC041, "113,403"
MRRG, CO27, SC044, "171,460"
MRRG, CO27, SC048, "75,499"
MSRG, CO12, SC018, "57,300"
MSRG, CO12, SC036, "30,811"
MSRG, CO12, SC050, "2,332"
MSRG, CO13, SC035, "61,139"
MSRG, CO13, SC047, "113,798"
SSRG, CO15, SC006, "214,051"
SSRG, CO16, SC012, "51,303"
SSRG, CO17, SC005, "78,343"
SSRG, CO17, SC009, "8,931"
SSRG, CO18, SC020, "2,264"
SSRG, CO18, SC045, "153,456"
FRRG, CO08, SC015, "195,038"
MCRG, CO19, SC027, "342,694"
MCRG, CO22, SC019, "159,519"
MCRG, CO32, SC017, "120,158"
MCRG, CO32, SC029, "148,662"
MCRG, CO35, SC031, "429,387"
MCRG, CO39, SC049, "128,215"
MCRG, CO40, SC037, "210,830"
MKRG, CO23, SC030, "170,500"
MKRG, CO24, SC010, "290,005"
MRRG, CO27, SC041, "111,330"
MSRG, CO13, SC035, "108,605"
];
Hi,
Try like this using Group By
Regards,
Jagan.
Dear Settu
Above Inline data field not relavant but i have more no of amout. i was send a sample data to you. and i get a data from oracle data base, please give me a another one.
thanks for your reply
sagara
Sorry sagara.. i don't understand what do you mean "another one"..
May be like the below
T1:
Select Fields from DataSource;
NoConcatenate
T2:
Load REGION,BCOD,SCCOD,
SUM(GROSSTOT) as GROSSTOT,
if(Sum(GROSSTOT)<=100000,'Below_10000',
if(Sum(GROSSTOT)>100001,'OVER_100000',0)) as AGE Resident T1 Group by REGION,BCOD,SCCOD;
DROP Table T1;
Hi,
please don't post the same questions multiple times and try to close your threads if you received correct answers.
Below 100,000 & Above 100,000
thanks
regards
Marco