How to group dimension valued based on different grouping rules

[#1]

Hi All,

There is a lot discussions about grouping, however I didn't find a useful stuff regarding my problem, so please help.

Here is the situation.

I have loaded from database few dimensions, among them the "CODE" representing material code in our system (excel, Column A)

As you see, the CODEs have the same syntax, however they contain similar materials under different code numbers. I want to create a new dimension, being a simple group of similar codes, so that I can click, say "A" getting the codes marked in yellow (columns F-G). I want to create such dimension in a way that if the code is within some grouping, the CODE_GROUP would show me the group name ("A", "B", etc), if the code is not in the group, the CODE_GROUP would show the value stored in "Material_Name" dimension (uploaded together with CODE).

Problems:

1. As you can see the grouping is based on different logics. Once it is that first 3 digits of CODE should fulfill some criteria (should be equal to 01-), then it could be that first 6 digits should fulfill some criteria, etc. Basically the grouping logic is always "some part of the CODE should be equal to something". I don't know how to easily make such grouping dimension workable. What I worked our is the very long "if" statement (If(Left(CODE;3) = '01-" , 'A', if(Left(CODE,3)='06-','A' etc etc) if I do this that way, my if statement will take 3 pages and ages to write the formula - it must be easier way

2. if I find the way to group CODEs based on different number of characters starting from left side of code value, then how should I state to the new variable (CODE_GROUP) that if the code string does not fulfill any grouping criteria, then assign the value for this code kept in some other dimension.

thanks a lot for any hint

Robert

[settu_periasamy]

Hi Robert,

At a one time work , you can create the Code Group and Code Group Name in the excel (check the attachment)

then you can simply use the applymap..

Like

Directory;

MAP_CODE:

MAPPING LOAD [CODE GROUP],

    [CODE GROUP NAME]

FROM

[..\temp_comm\Dimension_Grouping.xls]

(biff, embedded labels, table is Sheet2$);

T1:

LOAD CODE,ApplyMap('MAP_CODE',Left(CODE,6),ApplyMap('MAP_CODE',Left(CODE,3),'Unknown')) as Group

FROM

[..\temp_comm\Dimension_Grouping.xls]

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

Sample attached.

[#3]

Thanks a lot Sethu P, this moves my case in the right direction.

Question- is it possible to modify the code in a way that for CODEs not grouped in additional excel, a value from different dimension is used in CODE GROUP (replacing Unknown in your test.qvw

[settu_periasamy]

sorry, i don't understand your question.

can you give some more details?

[#5]

Yes, sure.

Lets take code no 10-110-05-0 . Now it is in a group called 'Unknown', because this code is not present in linking excel.

I would like in such case (code not present in linking excel) the dimension "Group" to store a value for such code, that is stored in dimension called "material_name"

(say for 10-110-05-0, the dimension "material name" keeps a value called "packing_machine" - I would like to get "Packing_Machine" instead of "unknown in a Group dimension for such code)

Brgds,

Robert

[settu_periasamy]

Yes, you can change the 'Unknown' as 'Packing_Machine'.. like

T1:

LOAD CODE,ApplyMap('MAP_CODE',Left(CODE,6),ApplyMap('MAP_CODE',Left(CODE,3),'Packing_Machine')) as Group

FROM

[..\temp_comm\Dimension_Grouping.xls]

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

Hope you already created all the group code.

[#7]

Hi,

I am sorry, I was not specific enough again.

Your ApplyMap formula as I understand it, says: for all CODEs missing value in the Dimension_Grouping.xls, put a name 'Packing_Machine' in Group dimension.

I need something like: for all CODEs missing value in the Dimension_Grouping.xls, put a value stored in "material_name" dimension

what I want to get is the following:

Dimension_Grouping excel:

Code     Code_Group

1          A

2          B

3         C

Data Loaded from DB

Code   material_name   specification   supplier   etc

1         plate                      xxx

4         valve                      yyy

3         bearing                  zzz

5          ring

Now your formula will give me the following:

Code    Group

1           A

4           Packing_Machine

3          C

5          Packing_Machine

I would like it to give me something like this:

Code       Group

1           A

4           valve

3          C

5          ring

[settu_periasamy]

Ok.  got it.  You can create one more map table and you can use that instead of 'Packing Machiene'

Directory;

MAP_CODE:

MAPPING LOAD [CODE GROUP],

     [CODE GROUP NAME]

FROM

[..\temp_comm\Dimension_Grouping.xls]

(biff, embedded labels, table is Sheet2$);

MAP_UNKNOWN:

MAPPING LOAD Distinct [CODE GROUP],

     Material_Name

FROM

Source;

T1:

LOAD CODE,ApplyMap('MAP_CODE',Left(CODE,6),ApplyMap('MAP_CODE',Left(CODE,3),

                        ApplyMap('MAP_UNKNOWN',CODE,'Unknown'))) as Group

FROM

[..\temp_comm\Dimension_Grouping.xls]

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