3 Replies Latest reply: Apr 26, 2011 3:24 AM by Gaurav Khare RSS

    Pivot table and grouping data

      I have a simple data but can't figure out on how to put the data together. This is what I have:

       

      Location

       

       

      Days

       

       

      Count

       

       

      Texas

       

       

      0

       

       

      10

       

       

      Texas

       

       

      1

       

       

      6

       

       

      Texas

       

       

      4

       

       

      3

       

       



      I want to group the Days by duration, here are the duration description:

       

      Duration

       

       

      At the same day

       

       

      <=0

       

       

       

      Next day

       

       

      <=1

       

       

       

      More than 1 day

       

       

      >1

       

       



      This is what I want to accomplish with pivot table:

       

      Location

       

       

      Duration

       

       

      Count

       

       

      Texas

       

       

      At the same day

       

       

      1

       

       

       

      Next day

       

       

      16

       

       

       

      More than 1 day

       

       

      19

       

       



      I've been browsing this forum but can't find any answer.

      Any help is appreciated! Thank you.

        • Pivot table and grouping data

          Hi,

          Data:
          LOAD * INLINE [
          Location, Days, Count
          Texas, 0, 10
          Texas, 1, 6
          Texas, 4, 3
          ];

          Let vToday = 0;
          Let vTodayMax= peek('Days',-1,Data);
          DaysRange:
          LOAD
          Range,
          Evaluate(Low)AS Low,
          Evaluate(High) AS High
          ;
          LOAD * INLINE [
          Range; Low; High
          At the same day;vToday ; vToday
          Next day; vToday; vToday+1
          More Than 1 day; vToday+1; vTodayMax
          ] (delimiter is ';')
          ;


          JOIN(DaysRange) IntervalMatch(Days) Load Low, High Resident DaysRange ORDER BY Low ASC;

           

          after running this script, create a pivot table with dimension as location,ranges and expresion as sum(count).

          It solves your problem...

           

          Niranjan M.

          • Pivot table and grouping data
            Rahul Gupta
            Hey Hi Dear, I have attached a test file as per your data.... Hope this contributes in solving your issue..!! Please have a look and let me know any further issues....!!
            • Pivot table and grouping data
              Gaurav Khare

              Hi Buddy,

              from your requirment is for certain that you need to have 2 dimensions namely location and duration and the expression will the count of something.....

              Now location we have and for the duration purpose you can do one thing... either go for calculated dimension or create the same in the script part itself..

              Goto add calculated dimension tab and in that write the script like- if(Days>1,'greater than 1',

              if(Days>4,'greater than 4', etc etc))..

              so now you have got two dimensions one is city and the second is Duration..Take these two dimensions in your pivot table and take expression as count of days.

              Hope you must have got an idea on how to proceed if in case you face any problem kindly send me a sample data and I will create an application for you.