7 Replies Latest reply: Oct 6, 2014 5:12 PM by Marco Wedel RSS

    Crosstable of crosstable/ crosstable with multiple criteria

      CategoryProductFouKeyJul'14Aug'14Sep'14Oct'14Nov'14Dec'14Jan'15Feb'15Mar'15Apr'15May'15Jun'15Jul'15Aug'15Sep'15Oct'15Nov'15Dec'15
      ADINGFTGFDINOFT Yld111198.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%98.5%
      BDINGFTGFDINOGDPW1111           1,279               1,295              1,310              1,316               1,317               1,317              1,345            1,345               1,345              1,363               1,363              1,363            1,382               1,382              1,382              1,382               1,382

                    1,382

       

       

      I am trying to transform above table into

       

      Category| Product | Fou | Key | Month | data

      A            | DIN      | GFT | GFD---| Sept'14| 98.5

      B            | DIN      | GFT | GFD---|Sept '14| 1310

       

      and so on. I used below code and could do it

      Directory;

      CrossTable(Month, Data, 4)

      LOAD Category,

           Product,

           Foundry,

           Key,

           [Jul'14],

           [Aug'14],

           [Sep'14],

           [Oct'14],

           [Nov'14],

           [Dec'14],

           [Jan'15],

           [Feb'15],

           [Mar'15],

           [Apr'15],

           [May'15],

           [Jun'15],

           [Jul'15],

           [Aug'15],

           [Sep'15],

           [Oct'15],

           [Nov'15],

           [Dec'15]

      FROM

      [FRODO_BAGH_AV_DINO_WIP_flush_9_3_14 (4).xlsx]

      (ooxml, embedded labels, table is YIELD);

       

      Now I want to chnage it to

       

      Product | Fou | Key    | Month   | A     | B

      DIN      | GFT | GFD---| Sept'14| 98.5 | 1310

       

      How do I do it?

        • Re: Crosstable of crosstable/ crosstable with multiple criteria
          Jonathan Poole

          Here is a load script that will restructure the table:

           

          Capture.PNG.png

           

           

           

          temp:

          CrossTable(Month, Values, 4)

          LOAD Category,

               Product,

               Fou,

               Key,

               [Jul'14],

               [Aug'14],

               [Sep'14],

               [Oct'14],

               [Nov'14],

               [Dec'14],

               [Jan'15],

               [Feb'15],

               [Mar'15],

               [Apr'15],

               [May'15],

               [Jun'15],

               [Jul'15],

               [Aug'15],

               [Sep'15],

               [Oct'15],

               [Nov'15],

               [Dec'15]

          FROM

          [http://community.qlik.com/thread/136624]

          (html, codepage is 1252, embedded labels, table is @1);

           

          NoConcatenate

          Data:

          Load

            Product,

            Month,

            Fou,

            Values as A,

            left(Key,3) & '---' as Key

          resident temp

          Where Category='A';

           

          left join (Data)

          load

            Product,

            Month,

            Fou,

            Values as B,

            left(Key,3) & '---' as Key

          Resident temp

          where Category='B';

           

          drop table temp;

            • Re: Crosstable of crosstable/ crosstable with multiple criteria
              Jonathan Poole

              And the following will be more generic, with no hard coding of category names (Same chart result).

               

              The Crosstable brings months into a vertical list and the generic load expands category and values into as many fields as there are unique category values.

               

              -----------

               

              temp:

              CrossTable(Month, Values, 4)

              LOAD Category,

                   Product,

                   Fou,

                   Key,

                   [Jul'14],

                   [Aug'14],

                   [Sep'14],

                   [Oct'14],

                   [Nov'14],

                   [Dec'14],

                   [Jan'15],

                   [Feb'15],

                   [Mar'15],

                   [Apr'15],

                   [May'15],

                   [Jun'15],

                   [Jul'15],

                   [Aug'15],

                   [Sep'15],

                   [Oct'15],

                   [Nov'15],

                   [Dec'15]

              FROM

              [http://community.qlik.com/thread/136624]

              (html, codepage is 1252, embedded labels, table is @1);

               

              Data:

              Generic load

                Product,

                Month,

                Fou,

                left(Key,3) & '---' as Key,

                Category,

                Values

              Resident temp;

               

               

              drop table temp;

            • Re: Crosstable of crosstable/ crosstable with multiple criteria
              Marco Wedel

              Hi,

               

              one solution might be also:

               

              QlikCommunity_Thread_136624_Pic1.JPG.jpg

               

              tabInput:
              CrossTable (MonthText,data,4)
              LOAD *
              FROM [http://community.qlik.com/thread/136624] (html, codepage is 1252, embedded labels, table is @1);
              
              Left Join (tabInput)
              LOAD Distinct
                MonthText,
                Date#(MonthText, 'MMM'&chr(39)&'YY') as Month
              Resident tabInput;
              
              Left Join (tabInput)
              LOAD Distinct
                Key,
                Left(Key,3)&'---' as KeyTemp
              Resident tabInput;
              
              DROP Fields MonthText, Key;
              
              RENAME Field KeyTemp to Key;
              
              tabOutput:
              Generic LOAD
                Product,
                Fou,
                Key,
                Month,
                Category,
                data
              Resident tabInput;
              
              DROP Table tabInput;
              

               

              I tried to create a solution that doesn't include hard coded column names in order to work for input tables with different month columns as well. This solution also creates a real date field "Month" to be able to sort and calculate by/with this field.

               

              hope this helps

               

              regards

               

              Marco