1 Reply Latest reply: Jul 25, 2016 12:23 PM by Sunny Talwar RSS

    Suggestions required

    Nitin Gupta

      Hi All,

       

      I want to have cross table but data is bifurcated into multiple columns as attached in the sample.

       

      Means when the crosstable is done the Date part and the qty part can be derived accordingly but the linking of Date with qty is also broken.

       

      Kindly suggest how to achieve.

       

      What I am doing is this:

      A:

      CrossTable(Type,Data,5)

      LOAD     [Part No],

           Plant,

           [Plant Short Name],

           Supplier,

           [Model Yr],

           [Release Week-1 Date],

           [Release Week-1 Qty],

           [Release Week-2 Date],

           [Release Week-2 Qty],

           [Release Week-3 Date],

           [Release Week-3 Qty],

           [Release Week-4 Date],

           [Release Week-4 Qty],

           [Release Week-5 Date],

           [Release Week-5 Qty],

           [Release Week-6 Date],

           [Release Week-6 Qty],

           [Release Week-7 Date],

           [Release Week-7 Qty],

           [Release Week-8 Date],

           [Release Week-8 Qty],

           [Release Week-9 Date],

           [Release Week-9 Qty],

           [Release Week-10 Date],

           [Release Week-10 Qty],

           [Release Week-11 Date],

           [Release Week-11 Qty],

           [Release Week-12 Date],

           [Release Week-12 Qty],

           [Release Week-13 Date],

           [Release Week-13 Qty],

           [Release Week-14 Date],

           [Release Week-14 Qty]

       

      From Table;

       

      Final:

      LOAD [Part No],

           Plant,

           [Plant Short Name],

           Supplier,

           [Model Yr]

           Data ,

            if(SubField(Type,' ',3)='Date','Date_Type','Qty_Type') as Type,

            if(SubField(Type,' ',3)='Date',Data,null()) as Release_Date,

            if(SubField(Type,' ',3)='Qty',Data,null()) as Release_Qty

          

      Resident A;

      drop table A;

        • Re: Suggestions required
          Sunny Talwar

          How about like this:

           

          A:

          CrossTable(Type,Data,5)

          LOAD *

          FROM

          [..\..\..\Downloads\Sample (3).xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          Final:

          LOAD KeepChar(Type, '0123456789') as Key,

            [Part No],

              Plant,

              [Plant Short Name],

              Supplier,

              [Model Yr],

              Date(Num#(Data)) as Release_Date

          Resident A

          Where SubField(Type,' ',3) = 'Date';

           

          Left Join (Final)

          LOAD KeepChar(Type, '0123456789') as Key,

            [Part No],

              Plant,

              [Plant Short Name],

              Supplier,

              [Model Yr],

              Data as Release_Qty

          Resident A

          Where SubField(Type,' ',3) = 'Qty';

           

          DROP Table A;