How to group numbers in range

[#1]

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

[swuehl]

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

[#3]

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

[swuehl]

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$);

[MarcoWedel · Accepted solution]

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

[Anonymous]

Use Class function.