1 Reply Latest reply: Jul 29, 2012 3:54 PM by Stefan Wühl RSS

    Aggregating data within the same table but different dimensions

      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 1Dimension 2Dimension 3Dimension 4Dimension 5Path length
      XXXXXXX1Channel 1channel 1 2
      XXXXXXX2Channel 2channel 4 2
      XXXXXXX3Channel 3 channel 4 2
      XXXXXXX4Channel 3 channel 3 channel 3 channel 1channel 35
      XXXXXXX5channel 2channel 1Channel 2channel 3 4
      XXXXXXX6channel 4 channel 1channel 1 3
      XXXXXXX7channel 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

        • Re: Aggregating data within the same table but different dimensions
          Stefan Wühl

          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)