how to group dimension items

[#1]

See attached. I want to group my dimension items into ABE = {'A','B','E'} and IJ = {'I','J'} so that in my Dimension I will have ABE, IJ, and any other item in the fields Dimension not grouped

[sunny_talwar]

May be this:

load *,

  If(Match(DIMENSION, 'A', 'B', 'E'), 'ABE',

  If(Match(DIMENSION, 'I', 'J'), 'IJ', DIMENSION)) as NEW_DIMENSION;

load * inline

[

DIMENSION

  A

  B

  C

  D

  E

  F

  G

  H

  I

  J

  K

  L

  M

  N

  O

  P

  Q

];

[#3]

If I still want to see the items after been grouped in same field like this:

A,

B,

C,

ABC,

What do I do?

[sunny_talwar]

May be this:

Table:

LOAD * Inline [

DIMENSION

  A

  B

  C

  D

  E

  F

  G

  H

  I

  J

  K

  L

  M

  N

  O

  P

  Q

];

Concatenate (Table)

LOAD DISTINCT Only(DIMENSION) as DIMENSION

Where Len(Trim(DIMENSION)) > 0

Group By DIMENSION;

LOAD If(Match(DIMENSION, 'A', 'B', 'E'), 'ABE',

  If(Match(DIMENSION, 'I', 'J'), 'IJ')) as DIMENSION

Resident Table;

[#5]

I had issues doing a =Sum(Figure) on the expression all the Grouped where 0 and the others has values. What do I do?

[Gysbert_Wassenaar]

Like this:

Table1:

LOAD * INLINE [

DIMENSION

  A

  B

  C

  D

  E

  F

  G

  H

  I

  J

  K

  L

  M

  N

  O

  P

  Q

];

Table2:

LOAD

    DIMENSION,

    DIMENSION as NEW_DIMENSION

RESIDENT

    Table1

;

CONCATENATE (Table2)

LOAD

    'ABE' as NEW_DIMENSION,

    DIMENSION

RESIDENT

    Table1

WHERE

    Match(DIMENSION, 'A','B','E')

    ;

Then use NEW_DIMENSION as your chart dimension.

[#7]

Yes, I have several groups to create not only ABE and it didn't work this way.

[sunny_talwar]

What about this?

Table:

LOAD * Inline [

DIMENSION, Number

  A, 23

  B, 67

  C, 4

  D, 7

  E, 9

  F, 8

  G, 55

  H, 50

  I, 22

  J, 44

  K, 43

  L, 67

  M, 89

  N, 34

  O, 34

  P, 19

  Q, 89

];

Concatenate (Table)

LOAD 'ABE' as DIMENSION,

  Sum(Number) as Number

Resident Table

Where Match(DIMENSION, 'A', 'B', 'E');

Concatenate (Table)

LOAD 'IJ' as DIMENSION,

  Sum(Number) as Number

Resident Table

Where Match(DIMENSION, 'I', 'J',);

[maxgro]

maybe

Table1:

LOAD * INLINE [

DIMENSION

  A

  B

  C

  D

  E

  F

  G

  H

  I

  J

  K

  L

  M

  N

  O

  P

  Q

];

Table2:

LOAD

    DIMENSION,

    DIMENSION as NEW_DIMENSION

RESIDENT

    Table1

;

for Each v in 'ABE', 'IJ'

  CONCATENATE (Table2)

  LOAD

  '$(v)' as NEW_DIMENSION,

    DIMENSION

  RESIDENT Table1

  WHERE SubStringCount('$(v)', DIMENSION);

NEXT;

[MarcoWedel]

Hi,

another solution might be:

YourFactTable:
LOAD RecNo() as ID,
    Chr(65+Floor(Rand()*26)) as DIMENSION,
    Ceil(Rand()*100) as SomeFact1,
    Ceil(Rand()*50) as SomeFact2
AutoGenerate 100;

mapGroups:
Mapping LOAD * Inline [
dimension, group
A,ABE
B,ABE
E,ABE
I,IJ
J,IJ
M,MNOP
N,MNOP
O,MNOP
P,MNOP
];

tabDIMENSION:
LOAD Distinct
    DIMENSION,
    Pick(IterNo(),DIMENSION,ApplyMap('mapGroups',DIMENSION)) as DIMENSION2
Resident YourFactTable
While IterNo()<3;

hope this helps

regards

Marco