2 Replies Latest reply: Oct 2, 2012 10:19 AM by nineworks RSS

    Group columns in straight/pivot table

      Hi everyone,

       

      I'm looking to create a table (straight or pivot) that contains a grouped column. I think some example data might explain it better. The data I've loaded into QlikView contains like the below:

       

      ABCD
      E
      TextMore TextFurther TextType 1Text
      TextMore TextFurther TextType 2Text
      aaabbbcccType 1ddd
      eeefffgggType 1hhh
      iiijjjkkkType 2lll

       

      I want the column D to be grouped/pivoted/etc so that the data looks like the below:

       

      ABCD
      E
      TextMore TextFurther TextType 1/Type 2 (or dropdown/twist)Text





      aaabbbcccType 1ddd
      eeefffgggType 1hhh
      iiijjjkkkType 2lll

       

      I'm not sure whether to do this in the load of the data, or using the pivot table. I've tried using both, but I can't get the effect I want. I can make all columns pivot, but not just one.

       

      Cheers

        • Re: Group columns in straight/pivot table
          Fernando Suzuki

          you could do that in script using something like this:

           

          LOAD A,

                B,

                C,

                concat(D, '/') AS D,

                E

          FROM XXXXXXXX

          Group by A, B, C, E;

           

           

          Hope this helps.

          Fernando

            • Re: Group columns in straight/pivot table

              That doesn't work for me, but I think that's because I've also got a fairly sizeable filter in my load script:

              filters(

              Remove(Row, RowCnd(Compound,

                        RowCnd(CellValue, 6, StrCnd(null)),

                        RowCnd(CellValue, 8, StrCnd(null))

              )),

              Replace(7, top, StrCnd(null)),

              Replace(6, top, StrCnd(null)),

              Replace(5, top, StrCnd(null)),

              Replace(4, top, StrCnd(null)),

              Replace(3, top, StrCnd(null)),

              Replace(2, top, StrCnd(null)),

              Replace(9, top, StrCnd(null)),

              Replace(10, top, StrCnd(null)),

              Replace(8, bottom, StrCnd(null))

              ))

               

              Column 8 is the one I want to concantanate, but it concats everything BUT column 8 when I use your method.