Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Multiple data columns to one

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

5 Replies
mhartono
Contributor

Re: Multiple data columns to one

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.

MVP
MVP

Re: Multiple data columns to one

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

];

MVP
MVP

Re: Multiple data columns to one

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

2016-02-07 #1.PNG

MVP
MVP

Re: Multiple data columns to one

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

you could also add a preceding load like

LOAD *

Where Len(Group);

regards

Marco

Community Browser