Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Best Way to Breakout Balance Field Incrementally

I was just curious, as it comes up a lot, but is there an easier way to create a balance range than the script below? We also use an input box version where to enter the range you want, but then if we end up with too many calculated dimensions in a report using that input box, it really affects the performance of the report.  So, is there an easier function to take something like a balance and just say break it out in $100 increments as a new field? Look forward to hearing how others tackle this. 

 

IF(ACCT_BALANCE<=200,'<200',
IF(ACCT_BALANCE>=200 AND ACCT_BALANCE<=400,'200-400',
IF(ACCT_BALANCE>400 AND ACCT_BALANCE<=600,'400-600',
IF(ACCT_BALANCE>600 AND ACCT_BALANCE<=800,'600-800',
IF(ACCT_BALANCE>800 AND ACCT_BALANCE<=1000,'800-1000',
IF(ACCT_BALANCE>1000 AND ACCT_BALANCE<=1500,'1000-1500',
IF(ACCT_BALANCE>1500,'1500+'
))))))) AS BalanceRange

Labels (6)
1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

My code above, wont handle 1500+. Please check the below.

tab1:
LOAD *, If(ACCT_BALANCE>=1500, Dual('1500+',ACCT_BALANCE),
     Dual(Replace(Class(ACCT_BALANCE,100),'<= x <','-'),Class(ACCT_BALANCE,100))) as BalanceRange;
LOAD Ceil(Rand()*2000) As ACCT_BALANCE
AutoGenerate 25;

commQV24.PNG

View solution in original post

8 Replies
Highlighted
Specialist II
Specialist II

Check the Class function.  You can use a replace statement to change the "<= x <" to just a dash.

Highlighted
Specialist III
Specialist III

With Class:

tab1:
LOAD *,
     Dual(Replace(Class(ACCT_BALANCE,100),'<= x <','-'),Class(ACCT_BALANCE,100)) as BalanceRange;
LOAD Ceil(Rand()*1000) As ACCT_BALANCE
AutoGenerate 25;

commQV23.PNG

Highlighted
MVP
MVP

If you have fixed interval for buckets then you can use class function. See below link for the same

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Condi...

If you want to create custom bucket interval then use below method

Data:
Load * Inline [
ACCT_BALANCE
100
150
210
350
430
550
630
750
810
990
1100
1302
1400
1600
1700 ];

Buckets:
load * inline [
From,To,Bucket
0,200,<200
200,400,200-400
401,600,400-600
601,800,600-800
801,1000,800-1000
1001,1500,1000-1500
1501,E99,>1500 ];

Left Join(Buckets)
IntervalMatch(ACCT_BALANCE)
Load From,To 
Resident Buckets;
Highlighted
Specialist III
Specialist III

My code above, wont handle 1500+. Please check the below.

tab1:
LOAD *, If(ACCT_BALANCE>=1500, Dual('1500+',ACCT_BALANCE),
     Dual(Replace(Class(ACCT_BALANCE,100),'<= x <','-'),Class(ACCT_BALANCE,100))) as BalanceRange;
LOAD Ceil(Rand()*2000) As ACCT_BALANCE
AutoGenerate 25;

commQV24.PNG

View solution in original post

Highlighted
Creator III
Creator III

This looks very interesting, never seen this function layout. I am going to try it out and get back to you. Thanks 

Highlighted
Creator III
Creator III

Capture.PNG

 @saran7de So I like this and it seems to be a great solution to my previous method. The only problem is that the set cap of 5000+ that I put in shows up for each record instead of grouping them when I add it as a selectable field. (See image).

All other records fall under the numeric ranges, but each one set as 5000+ shows individually.

Highlighted
Creator III
Creator III

I had to remove the 5000+ section because it broke them all out individually instead of into a 5000+ bucket. But, doing it this way yout WILL get the entire breakout by whichever breakout you set... for example increments of 100

Highlighted
Specialist III
Specialist III

Can you try this?

tab1:
LOAD *, If(ACCT_BALANCE>=1500, Dual('1500+',Class(ACCT_BALANCE,1500)),
     Dual(Replace(Class(ACCT_BALANCE,100),'<= x <','-'),Class(ACCT_BALANCE,100))) as BalanceRange;
LOAD Ceil(Rand()*2000) As ACCT_BALANCE
AutoGenerate 25;