Aggregating data within the same table but different dimensions

[#1]

Dear Qlikview Experts,

I'm currently working on a data set that's derived from a excel table. I've given an example of the data structure below:

ID	Dimension 1	Dimension 2	Dimension 3	Dimension 4	Dimension 5	Path length
XXXXXXX1	Channel 1	channel 1				2
XXXXXXX2	Channel 2	channel 4				2
XXXXXXX3	Channel 3	channel 4				2
XXXXXXX4	Channel 3	channel 3	channel 3	channel 1	channel 3	5
XXXXXXX5	channel 2	channel 1	Channel 2	channel 3		4
XXXXXXX6	channel 4	channel 1	channel 1			3
XXXXXXX7	channel 4					1

The data lies in a single data table, but each exposure is into a dimension. I want to join the five dimension into a signle dimmension so i can aggregate the different channel, as shown below:

                         path length

Channel 1               6

Channel 2               3

Channel 3               6

Channel 4               4

How would i does this?

Many thanks in advance,

Christian

[swuehl]

Christian,

do you calculate the path length from the count of the individual channels over all dimensions? Not looking at the ID or dimensions?

If so, you could concatenate the dimension fields into one:

INPUT:

LOAD * from YourExcelTable;

LOAD ID, Dimension1 as Dimension resident INPUT;

LOAD ID, Dimension2 as Dimension resident INPUT;

LOAD ID, Dimension3 as Dimension resident INPUT;

LOAD ID, Dimension4 as Dimension resident INPUT;

LOAD ID, Dimension5 as Dimension resident INPUT;

drop table INPUT;

Then you should be able to create a table chart with dimension Dimension and expression count(Dimension)