topic Re: How to group numbers in range in QlikView

How to group numbers in range

Wed, 17 Jun 2015 22:04:31 GMT

Hello,

Find attached I have series of numbers and I want to cut them in group to form different fields e.g:

1111008 to 1111020 is Revenue, and 1210010 to 1210112 is Cost etc. How do I do this

Rgds

Ema

Re: How to group numbers in range

swuehl — Wed, 17 Jun 2015 22:15:52 GMT

The Excel file doesn't tell what the rule for grouping should be. Do you know the rule?

Let's assume it's grouping based on the 4 digits each number starts with, the three following are just subdiving this group.

Create a mapping table and map for grouping your values:

MAP:

MAPPING LOAD F1, F2

INLINE [

F1, F2

1111, Revenue

1210, Cost

];

LOAD YourField,

APPLYMAP('MAP', LEFT(YourField, 4),'No mapping found') as Group

FROM YourExcelFile.xls;

edit: Or is the rule that you have known ranges for a group? Then you could also look into INTERVALMATCH LOAD prefix (for now, in the Help file).

Re: How to group numbers in range

Wed, 17 Jun 2015 22:27:24 GMT

I stated condition 1111008 to 1111020 is Revenue, and 1210010 to 1210112 is Cost etc

Re: How to group numbers in range

swuehl — Wed, 17 Jun 2015 22:47:37 GMT

Yes, you did.

MAP:

MAPPING

LOAD Start+iterno()-1 as F1, Group

INLINE [

Start, End, Group

1111008, 1111020, Revenue

1210010, 1210112, Cost

]

WHILE Start+iterno()-1 <= End;

Data:

LOAD @1 As Value,

ApplyMap('MAP', @1, 'no mapping found') as Group

FROM

[.\LB17_20150617_163655.xls]

(biff, no labels, table is Sheet1$);

Re: How to group numbers in range

MarcoWedel — Wed, 17 Jun 2015 23:23:04 GMT

Hi,

another solution could be:

tabNumbers:
LOAD @1 as number
FROM [https://community.qlik.com/servlet/JiveServlet/download/800360-170598/LB17_20150617_163655.xls] (biff, no labels, table is Sheet1$);

tabGroups:
LOAD * INLINE [
    from, to, groupname
    1111008, 1111020, Revenue
    1210010, 1210112, Cost
];

tabLink:
IntervalMatch (number)
LOAD from, to
Resident tabGroups;

hope this helps

regards

Marco

Re: How to group numbers in range

Anonymous — Thu, 18 Jun 2015 00:01:16 GMT

Use Class function.