BandWidth

[krishna20]

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

[krishna20]

Hi

  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

[krishna20]

Hi Friends,

Any Idea !!!

[krishna20]

Hi Experts,

Any Idea regarding this issue.

[#5]

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,

[krishna20]

Hi Harsha,

Thank you very much for your reply.

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

[#7]

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;

[krishna20]

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.

[#9]

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

[krishna20]

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.