4 Replies Latest reply: Mar 31, 2011 8:39 AM by Anand S RSS
      • How create Pivot table for this sample file

        I basically added in this their are columns like this i want report like this.....

         

        Week ending repair case Week ending avg subs

        Market 19-feb 26-feb 5-mar 12-mar 19-mar 19-feb 26-feb 5-mar 12-mar 19-mar

        A 12 7 10 8 10 182 185 192 201 204

        B

        C

         

          • How create Pivot table for this sample file
            CheenuJanakiram

            Hi Anand,

            Looks like you need to split your crosstable function, something along the lines of (also use the Header offset in the Data load UI to get rid of the first row)...
            Table1:
            LOAD
            B
            From PivotTable.xls;

            RepairCase_Temp:
            crosstable(WeekendRepairCase, Data, 1)
            Load
            Market,
            @2 as 19-Feb,
            @3 as 26-Feb,
            @4 as 03-Mar,
            @5 as 12-Mar,
            @6 as 19-Mar
            From PivotTable.xls;

            left join(Table1)
            LOAD
            Resident RepairCase_Temp;


            AvgSubs_Temp:
            crosstable(WeekendAvgSubs, Data, 1)
            Load
            Market,
            @2 as 19-Feb,
            @3 as 26-Feb,
            @4 as 03-Mar,
            @5 as 12-Mar,
            @6 as 19-Mar
            From PivotTable.xls;

            left join(Table1)
            LOAD
            Resident AvgSubs_Temp;

             

            The problem is your columns names are the same, that's why you need to use the @ to reference the column number. You can use the crosstable function multiple times, with a left join. All you need to do is load straight columns in one load statment, use crosstable function on one part of the source file, left join to the previous table, use crosstable on next part, left join, so on and so forth.

            If you keep adding columns as the months go by, you will need a more "intelligent" (automated) piece of script. Can't help you on that, it would take more time than I want to spend answer this thread.

            Parameters for crosstable function are as follows:

            crosstable(X, Y, Z), where X is the fieldname you want to give to the column headers you are unpivoting, Y is the fieldname you want to give of the data part of the pivoted part of the table, and Z is the number of qualified fields (i.e. the number of columns you are loading in that statement which are already in column format, i.e. typically the row header already contains the fieldname, not a field value).

            Hope this helps.

              • How create Pivot table for this sample file
                CheenuJanakiram

                Sorry, slight typo, script must read:

                Table1:
                LOAD
                Market
                From PivotTable.xls;

                RepairCase_Temp:
                crosstable(WeekendRepairCase, Data, 1)
                Load
                Market,
                @2 as 19-Feb,
                @3 as 26-Feb,
                @4 as 03-Mar,
                @5 as 12-Mar,
                @6 as 19-Mar
                From PivotTable.xls;

                left join(Table1)
                LOAD
                Resident RepairCase_Temp;


                AvgSubs_Temp:
                crosstable(WeekendAvgSubs, Data, 1)
                Load
                Market,
                @2 as 19-Feb,
                @3 as 26-Feb,
                @4 as 03-Mar,
                @5 as 12-Mar,
                @6 as 19-Mar
                From PivotTable.xls;

                left join(Table1)
                LOAD
                Resident AvgSubs_Temp;

                If you load the pivot table from a data source, you can use the UI (with associated colour coding for building the crosstable function) via the CrossTable... button.

            • How create Pivot table for this sample file

               

              Week ending repair case Week ending avg subs

              Market 19-feb 26-feb 5-mar 12-mar 19-mar 19-feb 26-feb 5-mar 12-mar 19-mar

              A 12 7 10 8 10 182 185 192 201 204

              B

              C



              Good explain but data is come from this columns like:-

              Market, Created date, Case Number, Customer No

              A 1/2/2011 6:22 00228402 6000036134

              B 1/2/2011 6:48 00228407 6000037219

              C 1/2/2011 7:36 00228413 6000032951

              D 1/2/2011 7:43 00228416 6000030740

              E 1/2/2011 7:54 00228422 6000037378

               

              I need to show "Week ending repair case" repair cases "Week ending avg subs"

              Please explain this example in *.QVW file.