topic Re: below above in QlikView

below above

sagaraperera — Fri, 09 Oct 2015 02:55:50 GMT

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

Re: below above

settu_periasamy — Fri, 09 Oct 2015 03:02:14 GMT

Hi,

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

Re: below above

sagaraperera — Fri, 09 Oct 2015 03:14:04 GMT

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

Re: below above

settu_periasamy — Fri, 09 Oct 2015 03:26:04 GMT

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?

Re: below above

sagaraperera — Fri, 09 Oct 2015 04:21:37 GMT

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

Re: below above

settu_periasamy — Fri, 09 Oct 2015 04:49:43 GMT

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"

];

Re: below above

jagan — Fri, 09 Oct 2015 05:28:12 GMT

Hi,

Try like this using Group By

Load REGION,BCOD,SCCOD,
Sum(GROSSTOT) as GROSSTOT,
if(Sum(GROSSTOT)<=100000,'Below_10000',
if(Sum(GROSSTOT) >= 100001,'OVER_100000','NA')) as AGE
FROM DataSource
Group by REGION,BCOD,SCCOD;

Regards,

Jagan.

Re: below above

sagaraperera — Fri, 09 Oct 2015 06:26:29 GMT

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

Re: below above

settu_periasamy — Fri, 09 Oct 2015 06:42:32 GMT

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;

Re: below above

MarcoWedel — Tue, 20 Oct 2015 21:30:42 GMT

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

below above

Below & above

thanks

regards

Marco

Re: below above

MarcoWedel — Tue, 20 Oct 2015 21:58:30 GMT

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