Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sagaraperera
Creator
Creator

below above

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

10 Replies
settu_periasamy
Master III
Master III

Hi,

Can you post the sample lines which is giving the mixed result?

sagaraperera
Creator
Creator
Author

below 100,000

 

REGIONBCODSCCODGROSSTOT
FRRGCO06SC007135,084
FRRGCO06SC025106,610
FRRGCO06SC028218,449
FRRGCO06SC03411,630
FRRGCO08SC0159,136
FRRGCO08SC02287,612
FRRGCO09SC00476,824
FRRGCO10SC023-2,742
FRRGCO11SC014-206,513
FRRGCO11SC042-50,632
FRRGCO25SC002173,466
FRRGCO25SC04630,875
MCRGCO19SC024181,179
MCRGCO19SC027305,096
MCRGCO20SC033102,545
MCRGCO20SC043119,490
MCRGCO21SC011162,159
MCRGCO22SC01942,859
MCRGCO30SC021153,930
MCRGCO32SC01774,888
MCRGCO32SC0296,059
MCRGCO35SC026-48,169
MCRGCO35SC031-237,657
MCRGCO39SC040-20,440
MCRGCO40SC037225,066
MKRGCO03SC0081,092,100
MKRGCO23SC030100,224
MKRGCO24SC01019,765
MKRGCO41SC01652,215
MKRGCO43SC0385,480
MRRGCO26SC039138,563
MRRGCO27SC00333,830
MRRGCO27SC03292,677
MRRGCO27SC041113,403
MRRGCO27SC044171,460
MRRGCO27SC04875,499
MSRGCO12SC01857,300
MSRGCO12SC03630,811
MSRGCO12SC0502,332
MSRGCO13SC03561,139
MSRGCO13SC047113,798
SSRGCO15SC006214,051
SSRGCO16SC01251,303
SSRGCO17SC00578,343
SSRGCO17SC0098,931
SSRGCO18SC0202,264
SSRGCO18SC045153,456
Total 4,325,750

Above 100,000

 

REGIONBCODSCCODGROSSTOT
FRRGCO08SC015195,038
MCRGCO19SC027342,694
MCRGCO22SC019159,519
MCRGCO32SC017120,158
MCRGCO32SC029148,662
MCRGCO35SC031429,387
MCRGCO39SC049128,215
MCRGCO40SC037210,830
MKRGCO23SC030170,500
MKRGCO24SC010290,005
MRRGCO27SC041111,330
MSRGCO13SC035108,605
Total 2,414,942

thanks

settu_periasamy
Master III
Master III

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..

Capture.JPG

is it possible to post to sample qvw?

sagaraperera
Creator
Creator
Author

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

 

REGIONBCODSCCODAGEAll_Product
FRRGCO06SC007Below_100000135,084
FRRGCO06SC025Below_100000106,610
FRRGCO06SC028Below_100000218,449
FRRGCO06SC034Below_10000011,630
FRRGCO08SC015Below_1000009,136
FRRGCO08SC015OVER_100000195,038
MCRGCO32SC029OVER_100000148,662
MCRGCO35SC026Below_100000-48,169
MCRGCO35SC031Below_100000-237,657
MCRGCO35SC031OVER_100000429,387
MCRGCO39SC040Below_100000-20,440
MCRGCO39SC049OVER_100000128,215
MCRGCO40SC037Below_100000225,066
MCRGCO40SC037OVER_100000210,830
MKRGCO03SC008Below_1000001,092,100
MKRGCO23SC030Below_100000100,224
MKRGCO23SC030OVER_100000170,500
MKRGCO24SC010Below_10000019,765
MKRGCO24SC010OVER_100000290,005
MKRGCO41SC016Below_10000052,215
MKRGCO43SC038Below_1000005,480
settu_periasamy
Master III
Master III

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"

];

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this using Group By

  1. Load REGION,BCOD,SCCOD, 
  2.      Sum(GROSSTOT) as GROSSTOT, 
  3.   if(Sum(GROSSTOT)<=100000,'Below_10000'
  4.   if(Sum(GROSSTOT) >= 100001,'OVER_100000','NA')) as AGE
  5. FROM DataSource
  6. Group by REGION,BCOD,SCCOD; 

Regards,

Jagan.

sagaraperera
Creator
Creator
Author

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

settu_periasamy
Master III
Master III

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;

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