Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
];
It will give you the following table where you can click for instance Z and get E, A and C:
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; |
you could also add a preceding load like
LOAD *
Where Len(Group);
regards
Marco