1 Reply Latest reply: Jun 16, 2017 2:35 PM by Sunny Talwar RSS

    Generate Dates based on Day names - Crosstable

    Ganesh Bagavathi

      Hello.

       

      I am looking to convert data into a linear table from crosstable structure and need help. My data looks like Sheet1 and my desired output is Sheet2 in the attached excel. Any help is appreciated.

       

      Thanks.

       

      Sheet1:

      Crosstable Sheet1.JPG

       

      Sheet2:

      Crosstable Sheet2.JPG

        • Re: Generate Dates based on Day names - Crosstable
          Sunny Talwar

          May be this

           

          MappingWeekDay:

          Mapping

          LOAD * Inline [

            WeekDay, WeekDayNum

            Sunday, 0

              Monday, 1

              Tuesday, 2

              Wednesday, 3

              Thursday, 4

              Friday, 5

              Saturday, 6

          ];

           

          Table:

          CrossTable (WeekDay, Value, 5)

          LOAD [Week starts on],

              Category,

              Name,

              Task,

              State,

              Sunday,

              Monday,

              Tuesday,

              Wednesday,

              Thursday,

              Friday,

              Saturday

          FROM

          [..\..\Downloads\Crosstable.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          FinalTable:

          LOAD *,

            ApplyMap('MappingWeekDay', WeekDay, Null()) as WeekDayNum,

            Date(RangeSum([Week starts on], ApplyMap('MappingWeekDay', WeekDay, Null()))) as Date

          Resident Table

          Where Value <> 0;

           

          DROP Table Table;