topic Re: BandWidth in QlikView

BandWidth

krishna20 — Mon, 23 Mar 2015 10:29:19 GMT

Hi Friends,

I'm Having a field as AMT_LC_1.

I need to set bandwidth for this amount by the limit range. I Have done as below.But, it's not working properly. I mean When i select the particular bandwidth, The paid amount should be displayed according to the range value. Please find the attached image for clear Idea.

if(AMT_LC_1<0 ,Dual('<0',1),

if(AMT_LC_1>1 and AMT_LC_1<=500000,Dual('1-500,000',2),

if(AMT_LC_1>=500001 and AMT_LC_1<=1000000,Dual('500,001-1,000,000',3),

if(AMT_LC_1>=1000001 and AMT_LC_1<=2000000,Dual('1,000,001-2,000,000',4),

if(AMT_LC_1>=2000001 and AMT_LC_1<=5000000,Dual('2,000,001-5,000,000',5),

if(AMT_LC_1>=5000001 and AMT_LC_1<=10000000,Dual('5,000,001-10,000,000',6),

if(AMT_LC_1>10000000,Dual('Above 10,000,000',7)))))))) as Band_width,

Please anyone suggest me.

Regards

Krishna

Re: BandWidth

krishna20 — Mon, 23 Mar 2015 16:32:47 GMT

IF(COMP_CODE='001' or COMP_CODE='002' or COMP_CODE='003' or COMP_CODE='004' or COMP_CODE='007' or COMP_CODE='009',

if(AMT_LC_1<0 ,Dual('<0',1),

if(AMT_LC_1>1 and AMT_LC_1<=500000,Dual('1-500,000',2),

if(AMT_LC_1>=500001 and AMT_LC_1<=1000000,Dual('500,001-1,000,000',3),

if(AMT_LC_1>=1000001 and AMT_LC_1<=2000000,Dual('1,000,001-2,000,000',4),

if(AMT_LC_1>=2000001 and AMT_LC_1<=5000000,Dual('2,000,001-5,000,000',5),

if(AMT_LC_1>=5000001 and AMT_LC_1<=10000000,Dual('5,000,001-10,000,000',6),

if(AMT_LC_1>10000000,Dual('Above 10,000,000',7))))))))) as Motor_Band,

Output:

IF(COMP_CODE='005' or COMP_CODE='006',

if(AMT_LC_1<0 ,Dual('<0',1),

if(AMT_LC_1>1 and AMT_LC_1<=50000,Dual('1-50,000',2),

if(AMT_LC_1>=50001 and AMT_LC_1<=100000,Dual('50,001-100,000',3),

if(AMT_LC_1>=100001 and AMT_LC_1<=200000,Dual('100,001-200,000',4),

if(AMT_LC_1>=200001 and AMT_LC_1<=500000,Dual('200,001-500,000',5),

if(AMT_LC_1>=500001 and AMT_LC_1<=1000000,Dual('500,001-1,000,000',6),

if(AMT_LC_1>1000000,Dual('Above 1,000,000',7))))))))) as Band_Claim,

Output:

Output is differing based on condition .Can any one please suggest me why output is differing..

Regards

Krishna

Re: BandWidth

krishna20 — Mon, 23 Mar 2015 18:27:16 GMT

Hi Friends,

Any Idea !!!

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 05:29:18 GMT

Hi Experts,

Any Idea regarding this issue.

Re: BandWidth

Tue, 24 Mar 2015 05:36:00 GMT

Try like below & see,

IF(COMP_CODE='001' or COMP_CODE='002' or COMP_CODE='003' or COMP_CODE='004' or COMP_CODE='007' or COMP_CODE='009',

if(sum(AMT_LC_1)<0 ,Dual('<0',1),

if(sum(AMT_LC_1)>1 and sum(AMT_LC_1)<=500000,Dual('1-500,000',2),

if(sum(AMT_LC_1)>=500001 and sum(AMT_LC_1)<=1000000,Dual('500,001-1,000,000',3),

if(sum(AMT_LC_1)>=1000001 and sum(AMT_LC_1)<=2000000,Dual('1,000,001-2,000,000',4),

if(sum(AMT_LC_1)>=2000001 and sum(AMT_LC_1)<=5000000,Dual('2,000,001-5,000,000',5),

if(sum(AMT_LC_1)>=5000001 and sum(AMT_LC_1)<=10000000,Dual('5,000,001-10,000,000',6),

if(sum(AMT_LC_1)>10000000,Dual('Above 10,000,000',7))))))))) as Motor_Band,

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 06:02:36 GMT

Hi Harsha,

Thank you very much for your reply.

The script is not executing.It's raising error as Invalid expression.

Re: BandWidth

Tue, 24 Mar 2015 06:03:51 GMT

You need to use group by for above case...

Existing_Tbl:

load

from source;

New_Tbl:

load

Primary_Key,

IF(COMP_CODE='001' or COMP_CODE='002' or COMP_CODE='003' or COMP_CODE='004' or COMP_CODE='007' or COMP_CODE='009',

if(sum(AMT_LC_1)<0 ,Dual('<0',1),

if(sum(AMT_LC_1)>1 and sum(AMT_LC_1)<=500000,Dual('1-500,000',2),

if(sum(AMT_LC_1)>=500001 and sum(AMT_LC_1)<=1000000,Dual('500,001-1,000,000',3),

if(sum(AMT_LC_1)>=1000001 and sum(AMT_LC_1)<=2000000,Dual('1,000,001-2,000,000',4),

if(sum(AMT_LC_1)>=2000001 and sum(AMT_LC_1)<=5000000,Dual('2,000,001-5,000,000',5),

if(sum(AMT_LC_1)>=5000001 and sum(AMT_LC_1)<=10000000,Dual('5,000,001-10,000,000',6),

if(sum(AMT_LC_1)>10000000,Dual('Above 10,000,000',7))))))))) as Motor_Band

resident Existing_Tbl group by Primary_Key;

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 06:18:42 GMT

Hi Harsha,

I should not sum overall amount in the back end script. I need to show the particular claim No's amount is belongs to this Bandwidth range.

For eg; If i select the range 1-50,000.

The claim No's Amount should display in the range 1- 50,000.

Re: BandWidth

Tue, 24 Mar 2015 06:28:13 GMT

Hi,

what field you are using for claim No's Amount is it AMT_LC_1...

if it is same then take your resulted bucket field in list-box & cross-check range field value with AMT_LC_1 ..

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 06:37:48 GMT

Hi.

Please find the sample app,

I'm not getting the second bandwidth range correctly, i,e; <0 and Above 1,000,000 in the list box.

This range amount should linked to company's CS_AMT_LC_1 based on Claim No.

Re: BandWidth

Tue, 24 Mar 2015 06:58:17 GMT

Hi Krishna,

Are you sure your range <0 and Above 1,000,000 is not matching with CS_AMT_LC_1 cause for your both band results looks ok...!

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 07:01:22 GMT

Hi,

In the Second range(Claim_Band) list box <0 and Above 1,000,000 is not displaying.That one is first issue.

Re: BandWidth

Tue, 24 Mar 2015 07:11:45 GMT

Put

CS_AMT_LC_1=<0

if you want to include 0 in range

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 07:21:27 GMT

Hi Harsha,

Thank you.

Perfect !! Displaying <0 is working by using <=0 . I will be having negative values in Sum(CS_AMT_LC_1) .In that scenario it won't works right? Above 1,000,000 also I need to display in this list box.

Re: BandWidth

Tue, 24 Mar 2015 07:29:55 GMT

Negative values in Sum(CS_AMT_LC_1) will return -ve value take text-box & put =Sum(CS_AMT_LC_1) in it ..

Now you want 'above 1,000,000' then should use >1,000,000

But may be you are using 'Above 10,000,000' for Claim_Band please check & let me know

if it is then use >10,000,000 simple case...

Re: BandWidth

Tue, 24 Mar 2015 07:45:09 GMT

Hi,

you are using straight table select expression & find total mode under expression select sum under dropdown for total..

in your dashboard there is no any data present for event field hence the total will be 0

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 07:50:08 GMT

Hi Harsha,

In the script i have used two bands .The above range is different from the below range. In the below range above 1,000,000 is not showing.

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 07:54:05 GMT

Hi Harsha,

I'm using Expression total in total mode. Its displaying total sum. It shouldn't be zero, because values are there in CS_AMT_LC_1 field.

Re: BandWidth

krishna20 — Tue, 24 Mar 2015 07:58:32 GMT

Hi Harsha,

I found that in the second bandwidth range script (Sample app) for the Above 1,000,000 ,if i used <= 1,000,000 it's displaying in list box. The other options are not working.Why may cause this issue? Any idea?

Re: BandWidth

Tue, 24 Mar 2015 08:31:29 GMT

Promote Event field it will give total..

IF(CS_COMP_CODE='005' or CS_COMP_CODE='006',

(if(CS_AMT_LC_1<=1 ,Dual('<0',1),

if(CS_AMT_LC_1>1 and CS_AMT_LC_1<=50000,Dual('1-50,000',2),

if(CS_AMT_LC_1>=50001 and CS_AMT_LC_1<=100000,Dual('50,001-100,000',3),

if(CS_AMT_LC_1>=100001 and CS_AMT_LC_1<=200000,Dual('100,001-200,000',4),

if(CS_AMT_LC_1>=200001 and CS_AMT_LC_1<=500000,Dual('200,001-500,000',5),

if(CS_AMT_LC_1>=500001 and CS_AMT_LC_1<=1000000,Dual('500,001-1,000,000',6),Dual('Above 1,000,000',7))))))))) as Band_Claim,

it will work