Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
newqlik2017
Contributor

Assign correct Range values in a chart

Hello,

Below is an example of my data set and what the final result should look like.

Policy 101 includes 2 locations and sum of Policy 101 = 50 + 40 = 90 which should be on 50-100 band

Policy 201 includes 3 locations and sum of Policy 201 = 100+250+100 = 450 which should be on 100-500 band
Policy 301 includes 1 location and sum of Policy 301 = 900 which should be on 500-1000 band

I also attached the qvw but the values are not being assigned to the correct band (range).

Any suggestions ?

Data Set
StatePolicyLocationAmount
PA101100150
PA101100240
PA2012001100
PA2012002250
PA2012003100
PA3013001900

 

Result
LowHighStatePolicyAmount
050PA00
50100PA10190
100500PA201450
5001000PA301900

1 Solution

Accepted Solutions
Jacek
Contributor

Re: Assign correct Range values in a chart

I think you should do IntervalMatch for grouped Amount at Policy level:

DATA:

LOAD *

INLINE [

State, Policy, Location, Amount

PA, 101, 1001, 50

PA, 101, 1002, 40

PA, 201, 2001, 100

PA, 201, 2002, 250

PA, 201, 2003, 100

PA, 301, 3001, 900

];


left join(DATA)

LOAD

State, Policy, sum(Amount) as AmountPolicy

Resident DATA

group by State, Policy;


BAND:

LOAD *

INLINE [

Low, High

0, 50

50, 100

100, 500

500, 1000

];


JOIN (DATA)

IntervalMatch(AmountPolicy)

LOAD

*

RESIDENT BAND;


DROP TABLE BAND;

View solution in original post

4 Replies
Jacek
Contributor

Re: Assign correct Range values in a chart

I think you should do IntervalMatch for grouped Amount at Policy level:

DATA:

LOAD *

INLINE [

State, Policy, Location, Amount

PA, 101, 1001, 50

PA, 101, 1002, 40

PA, 201, 2001, 100

PA, 201, 2002, 250

PA, 201, 2003, 100

PA, 301, 3001, 900

];


left join(DATA)

LOAD

State, Policy, sum(Amount) as AmountPolicy

Resident DATA

group by State, Policy;


BAND:

LOAD *

INLINE [

Low, High

0, 50

50, 100

100, 500

500, 1000

];


JOIN (DATA)

IntervalMatch(AmountPolicy)

LOAD

*

RESIDENT BAND;


DROP TABLE BAND;

View solution in original post

shiveshsingh
Honored Contributor

Re: Assign correct Range values in a chart

you can use interval match function.

newqlik2017
Contributor

Re: Assign correct Range values in a chart

In the current data set, is it possible to keep the table structure even though there's no data? Please see expected data set vs What's going on. When an appropriate filter is selected (let's say CITY for example), it drops the first two rows.

I tried using NULLASVALUE function but that didn't work.

SET NULLASVALUE = 'NULL';

NULLASVALUE *;

I also flipped the order but same result:

NULLASVALUE *;

SET NULLASVALUE = 'NULL';

Current Data Set

LowHighStatePolicyAmount
050PA00
50100PA10190
100500PA201450
5001000PA301900

Expected Data Set:

LowHighStatePolicyAmount
050
50100
100500PA201450
5001000PA301900

What's going on:

LowHighStatePolicyAmount
100500PA201450
5001000PA301900
Jacek
Contributor

Re: Assign correct Range values in a chart

It is not possible to see more than in your data model. You don't have zero values. IntervalMatch did not create ranges with nulls. You can try create another set of data with dummy ranges with zeros in Load Script.

range.png