topic Re: Multiple data columns to one in QlikView

Multiple data columns to one

Sun, 07 Feb 2016 12:21:39 GMT

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

Re: Multiple data columns to one

Anonymous — Sun, 07 Feb 2016 16:47:28 GMT

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.

Re: Multiple data columns to one

petter — Sun, 07 Feb 2016 16:56:16 GMT

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
];

Re: Multiple data columns to one

petter — Sun, 07 Feb 2016 16:58:24 GMT

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

Re: Multiple data columns to one

petter — Sun, 07 Feb 2016 17:04:43 GMT

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;

Re: Multiple data columns to one

MarcoWedel — Sun, 07 Feb 2016 22:44:05 GMT

you could also add a preceding load like

LOAD *
Where Len(Group);

regards

Marco