4 Replies Latest reply: Jun 5, 2013 5:20 AM by Hannan Tariq RSS

    Cross Table Solution required

      Hey,

      I've got a problem regarding cross table.

       

      I am getting data as shown in the following image:

      1.png

       

      But I want to display the data like this:

      2.png

       

      Daily active users, Monthly active users and Weekly active Users should be displayed in a row and there corresponding values should be displayed below them.

       

      Help me out.

       

      Regards,

      Hannan Tariq

        • Re: Cross Table Solution required
          Gysbert Wassenaar

          Create a pivot table chart with Date and Active Users as dimensions and sum(Value) as expression. Then pivot the Active Users dimension to horizontal position by dragging its header to the right above the expression. When you see a horizontal blue line you can drop it and it should be in place.

            • Re: Cross Table Solution required

              Thank You Gysbert, Your solution is helpful. I am able to accomplish thte task by some more modifications in the script. What I did is in the following script.

               

              LOAD

                Active Users,

                pacific_date as Date,

                Value as day

                FROM [Corresponding Source]

                where period ='day';

               

              join

              LOAD

                 Active Users,

                pacific_date as Date,

                Value as week

                FROM [Corresponding Source]

                where period ='week';

               

              join

              LOAD

                Active Users,

                pacific_date as Date,

                Value as month

                FROM [Corresponding Source]

                where period ='month';

               

              And then added the dimension Date in the Pivot table and sum(day), sum(week), sum(month) in the expressions and then tada.

               

              4.png

               

              Thank you for your help.

            • Re: Cross Table Solution required
              Sokkorn Cheav

              Hi Hannan,

               

              Use this load script

              [Data]:
              LOAD * Inline [
              Dates,        Active Users,        Value
              01-01-2013,    Daily Active Users,    1450
              01-01-2013,    Weekly Acive Users,    1658
              01-01-2013,    Monthly Active Users,    2054
              02-01-2013,    Daily Active Users,    1555
              02-01-2013,    Weekly Acive Users,    1875
              02-01-2013,    Monthly Active Users,    2474
              03-01-2013,    Daily Active Users,    1796
              03-01-2013,    Weekly Acive Users,    3002
              03-01-2013,    Monthly Active Users,    4012];
              
              [TMP1]:
              GENERIC LOAD * RESIDENT [Data];
              
              [RESULT]:
              LOAD DISTINCT Dates RESIDENT [Data];
              
              DROP TABLE [Data];
              
              FOR i = 0 to NoOfTables()
              TableList:
              LOAD '[' & TableName($(i)) &']' AS Tablename AUTOGENERATE 1
              WHERE WildMatch(TableName($(i)), 'TMP1.*');
              NEXT i
              
              FOR i = 1 to FieldValueCount('Tablename')
              LET vTable = FieldValue('Tablename', $(i));
              LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);
              DROP TABLE $(vTable);
              NEXT i
              
              DROP Table TableList;
              

              See sample attached file also.

               

              Regards,

              Sokkorn