topic Re: how to group dimension items in QlikView

how to group dimension items

Mon, 22 Feb 2016 13:42:52 GMT

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

Re: how to group dimension items

sunny_talwar — Mon, 22 Feb 2016 13:45:41 GMT

May be this:

load *,

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

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

load * inline

[

DIMENSION

];

Re: how to group dimension items

Mon, 22 Feb 2016 14:41:28 GMT

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

ABC,

What do I do?

Re: how to group dimension items

sunny_talwar — Mon, 22 Feb 2016 14:46:55 GMT

May be this:

Table:

LOAD * Inline [

DIMENSION

];

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;

Re: how to group dimension items

Mon, 22 Feb 2016 16:21:41 GMT

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

Re: how to group dimension items

Gysbert_Wassenaar — Mon, 22 Feb 2016 16:27:40 GMT

Like this:

Table1:

LOAD * INLINE [

DIMENSION

];

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.

Re: how to group dimension items

Mon, 22 Feb 2016 16:33:22 GMT

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

Re: how to group dimension items

sunny_talwar — Mon, 22 Feb 2016 18:42:37 GMT

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',);

Re: how to group dimension items

maxgro — Mon, 22 Feb 2016 18:57:28 GMT

maybe

Table1:

LOAD * INLINE [

DIMENSION

];

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;

Re: how to group dimension items

MarcoWedel — Mon, 22 Feb 2016 23:07:18 GMT

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