Multiple data columns to one

[#1]

Hi I have a data set like below

CustCode     Group1     GroupOther

A                    X               Z;;;;;;;

B                    X         

C                    Y               Z;;;;;;;;

D                    Y

E                    Z

Group1 is the basic link between customers but where a second set of grouping is required you can enter up to 8 further groups which are stored in GroupOther with a ; as the separator.

I know how to split the GroupOther fields during the load and give each field a name.

I need my final data so if I select Z from Group1 I fileter to E AND A AND C not just E

[Anonymous]

Try to create another table (e.g. "CustGroup) with foreign key to CustCode. Then in this this new table you have two columns: "CustCode" and "Group". The "Group" column should have combination of all applicable group for that one CustCode (one customer code to many group) e.g.

CustCode     Group

A                    X

A                    Z

B                    X

C                    Y

C                    Z

etc.

Obviously you create this "CustGroup" table using from your original table e.g. using crosstable.

Then use this "Group" field in your chart.

I hope this helps.

[petter]

You can do like this:

T:
LOAD
  CustCode,
  SubField( Group & ';' & GroupOther , ';' ) AS Group
;
LOAD * INLINE [
CustCode, Group, GroupOther
A, X , Z;R;S;T
B, X , T;U;V
C, Y, Z;L;M;N
D, Y
E, Z
];

[petter]

It will give you the following table where you can click for instance Z and get E, A and C:

[petter]

Changing the expression in the load script to this will make the groups with empty strings go away:

SubField( Group &  If( GroupOther <> '' , ';' & GroupOther ) , ';' ) AS Group;

[MarcoWedel]

you could also add a preceding load like

LOAD *
Where Len(Group);

regards

Marco