Creating range

[steve_br]

Hello!

I would like to create a line chart using the sample data beneath. I need to aggregate the kilograms into three groups: 0-3000, 3001-6000, 6001-9000 so that the chart shows the following information:

y-axis: costs per ton

x-axis: month

lines: cost development for each group

legend: carriers

I don't know how to create these three groups using the given ranges. Would appreciate any help. Thanks!

Carrier	KG	Cost	Date
Carrier 1	3836	14	21-03-16
Carrier 1	1590	15	20-02-16
Carrier 1	2403	16	06-01-16
Carrier 1	8341	9	16-03-16
Carrier 1	723	15	26-03-16
Carrier 1	5610	14	25-03-16
Carrier 1	5456	14	30-01-16
Carrier 1	8286	6	05-01-16
Carrier 1	1569	19	19-02-16
Carrier 1	6925	6	15-02-16
Carrier 1	2011	15	11-01-16
Carrier 1	6545	7	19-03-16
Carrier 1	1994	17	19-01-16
Carrier 1	3796	15	15-01-16
Carrier 1	2581	16	18-03-16
Carrier 1	1747	18	15-02-16
Carrier 1	5976	11	22-01-16
Carrier 1	5545	15	11-01-16
Carrier 1	2074	20	26-03-16
Carrier 1	8685	9	31-03-16
Carrier 1	8794	10	02-03-16
Carrier 1	1604	17	17-01-16
Carrier 1	6661	5	25-02-16
Carrier 1	6846	7	20-01-16
Carrier 1	3540	13	12-03-16
Carrier 1	4206	12	18-02-16
Carrier 1	3360	14	07-01-16
Carrier 1	1590	19	28-03-16
Carrier 1	3885	10	22-02-16
Carrier 1	2691	16	06-02-16
Carrier 2	5688	12	18-01-16
Carrier 2	1022	19	15-03-16
Carrier 2	6651	5	25-03-16
Carrier 2	2023	19	15-02-16
Carrier 2	3085	14	25-01-16
Carrier 2	5043	13	04-01-16
Carrier 2	3125	10	31-03-16
Carrier 2	4109	13	21-01-16
Carrier 2	5844	15	11-02-16
Carrier 2	4064	10	30-01-16
Carrier 2	2291	17	08-02-16
Carrier 2	7829	6	01-02-16
Carrier 2	6804	7	01-02-16
Carrier 2	3540	14	24-03-16
Carrier 2	816	18	09-01-16
Carrier 2	6486	6	10-01-16
Carrier 2	5348	11	26-03-16
Carrier 2	8914	7	07-03-16
Carrier 2	4863	12	15-03-16
Carrier 2	4227	10	06-02-16
Carrier 2	2114	16	05-02-16
Carrier 2	5028	14	30-03-16
Carrier 2	3173	11	17-03-16
Carrier 2	2382	16	09-03-16
Carrier 2	1021	20	05-01-16
Carrier 2	6096	5	01-03-16
Carrier 2	8820	9	28-01-16
Carrier 2	5816	12	07-02-16
Carrier 2	3737	10	26-02-16
Carrier 2	8369	6	26-01-16

[Gysbert_Wassenaar]

Here's one way:

LOAD

     Carrier,

     KG,

     Class(KG, 3000) as KG_Range

     Cost,

     Date

FROM

     ...source...

     ;

[steve_br]

Thank you, but I would prefer to create a new dimension with a specific expression and I don't wanna use the class function, because I also work on other cases where I need different grouping such as 0-1000, 1000-4000, 4000-9000. Would this be possible?

[oknotsen · Accepted solution]

I suggest you look into using the intervalmatch() function.

http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalMatch.h...‌

With this function you can setup your own ranges, which can even overlap if that would be of use for you.

Not behind my work PC, so no access to the examples I have there. If you would like some, let me know and I will post those tomorrow.

[steve_br]

Thank you for the suggestion! I will have a look at it.

Actually I just did something else and it seems to work:

Aggr(If(KG<3000, '<3t',

If(KG>=3000 and KG<6000, '3-6t',

If(KG>=6000 and KG<9000, '6-9t',

'9+'))),KG)

Is this also a correct solution?

[jonathandienst]

The calculated dimension will work, but you will get better performance* and easier maintenance if you perform an interval match in the load script.

* If your model is small (<1m rows), then the performance of the calculated dimension would probably be acceptable. If the model is large (>10m rows), then the performance will matter.

[steve_br]

Okay, great. Thank you all for the help!