7 Replies Latest reply: Feb 17, 2015 7:24 AM by Priyanka Rao RSS

    CrossTable

    Priyanka Rao


      Hi,

      I have a spreadsheet in the below format;Untitled.png

       

       

      There are sets of 'Particular' like 'Brand','Distribution'. etc such 5 and 'Variance','Score','Cal' against each of the 'Particular' repeating for 12 Months.

      The issue is with the Format it is placed.

      CrossTable might be  the solution, but unable to implement in a right manner.

       

      Any help appreciated!

      Thanks !

        • Re: CrossTable
          neetha P

          Hi,

           

          See Multi header pivot import http://community.qlik.com/docs/DOC-4527

           

          Regards

          Neetha

          • Re: CrossTable
            Tobias Klett

            Hi Priyanka,

             

            what you are looking for is not Crosstable but Transponse.

             

            Transponse:

            LOAD @1 as Month,

                 @2 as KPI,

                 @3 as Value

            FROM

            CrossTable_orNot.xlsx

            (ooxml, no labels, table is Crosstable, filters(

            Transpose()

            ));

             

            Hope this helps
            Regards Tobias

              • Re: CrossTable
                Priyanka Rao

                Hi,

                Appreciate the response.

                 

                But, If you observe, the Varience, Score and Cal fields, in your mentioned case 'KPI' are to be taken as 3 Different Fields, Also if you notice  they are repeating for every Month against it.

                 

                Can you please share any idea to deal with it?

                Thanks !

                  • Re: CrossTable
                    neetha P

                    Hi Priyanka,

                     

                    After transposing data as single field,group data to different fields like Varience, Score and Cal fields.

                     

                    Regards

                    Neetha

                    • Re: CrossTable
                      Tobias Klett

                      Hi Priyanka,

                       

                      change the script to:

                       

                      Transponse:

                      LOAD @1 as Month,

                      //     @2 as KPI,

                           @3 as Variance

                      FROM

                      CrossTable_orNot.xlsx

                      (ooxml, no labels, table is Crosstable, filters(

                      Transpose()

                      ))

                      where @2 = 'Variance';

                       

                      left join  (Transponse)

                      LOAD @1 as Month,

                      //     @2 as KPI,

                           @3 as Score

                      FROM

                      CrossTable_orNot.xlsx

                      (ooxml, no labels, table is Crosstable, filters(

                      Transpose()

                      ))

                      where @2 = 'Score';

                       

                      left join  (Transponse)

                      LOAD @1 as Month,

                      //     @2 as KPI,

                           @3 as Cal

                      FROM

                      CrossTable_orNot.xlsx

                      (ooxml, no labels, table is Crosstable, filters(

                      Transpose()

                      ))

                      where @2 = 'Cal';

                       

                      Regards Tobias

                        • Re: CrossTable
                          Priyanka Rao

                          Hi,

                          Thanks a lot for the reply, they have been very helpful !

                           

                          Another thing, If you notice in the Screen shot, there are multiple rows for the Values which contradicts the assumption '@3' being the only row containg value for each KPI, for each Month ..

                           

                          Also, there is a 'Particulars' Field which is the Main Metrics Field for each of the value present.

                           

                          Would like more of your insight on it !

                          Thanks in advance !