5 Replies Latest reply: Jun 5, 2013 5:38 AM by Kenneth Keary RSS

    Implementing Crosstable without using cross table for learning purposes

      Hi All,

      I'm trying to learn the structure of coding in the script environment. So i'm trying to implement crosstable functionality without using the crosstable function.

      Has anyone got an ideas as to how I should go about this.

       

      Initial Data layout would be like so

       

       

      YearProdIDTest1Test2

      Test3

       

      2003A451292
      2004B627811
      2005A768964

       

      Resultant Data Layout

       

       

       

      YearProdIDTestCaseResult
      2003ATest145
      2003ATest212
      2003ATest392
      2004BTest162
      2004BTest278
      2004BTest311
      2005ATest176
      2005ATest289
      2005ATest364

      Any help would be greatly appreciated.

      Thanks, Ken

        • Re: Implementing Crosstable without using cross table for learning purposes

          Hi Ken,

           

          Your Initial data and result the same..

          Maybe a mistake?

           

          Regards,

          Lester

              • Re: Implementing Crosstable without using cross table for learning purposes
                Sokkorn Cheav

                Hi Ken,

                 

                Try this script:

                1. If you want to user CrossTable

                ================================

                [Data]:

                LOAD * Inline [

                Year,    ProdID,    Test1,    Test2,    Test3

                2003,    A,        45,        12,        92

                2004,    B,        62,        78,        11

                2005,    A,        76,        89,        64];

                 

                [Data2]:

                CrossTable(Year,Value2,2)

                LOAD

                    Year    AS Years,

                    ProdID,

                    Test1,

                    Test2,

                    Test3

                Resident [Data];

                 

                RENAME Field [Year] to [Value1];

                 

                DROP Table [Data];

                ================================

                 

                2. If you don't want to use Cross Table

                ================================

                [Data]:

                LOAD * Inline [

                Year,    ProdID,    Test1,    Test2,    Test3

                2003,    A,        45,        12,        92

                2004,    B,        62,        78,        11

                2005,    A,        76,        89,        64];

                 

                [Data1]:

                LOAD

                    Year    AS NewYear,

                    ProdID    AS NewProdID,

                    'Test1'    AS Fields,

                    Test1    AS Value

                Resident [Data];

                 

                [Data2]:

                Concatenate ([Data1])

                LOAD

                    Year    AS NewYear,

                    ProdID    AS NewProdID,

                    'Test2'    AS Fields,

                    Test2    AS Value

                Resident [Data];

                 

                [Data2]:

                Concatenate ([Data1])

                LOAD

                    Year    AS NewYear,

                    ProdID    AS NewProdID,

                    'Test3'    AS Fields,

                    Test3    AS Value

                Resident [Data];

                 

                DROP Table [Data];

                ================================

                 

                For me I prefer Cross Table than second solution.

                 

                Regards,

                Sokkorn

                • Re: Implementing Crosstable without using cross table for learning purposes

                  Hi Ken,

                   

                  Here's what I did:

                   

                  Initial:
                  LOAD * INLINE [
                      Year, ProdID, Test1, Test2, Test3
                      2003, A, 45, 12, 92
                      2004, B, 62, 78, 11
                      2005, A, 76, 89, 64
                  ];


                  Result:
                  Load Year,
                    ProdID,
                    'Test1' as TestCase,
                    Test1 as Result
                  Resident Initial;

                   

                  concatenate
                  Load Year,
                    ProdID,
                    'Test2' as TestCase,
                    Test2 as Result
                  Resident Initial;

                   

                  concatenate
                  Load Year,
                    ProdID,
                    'Test3' as TestCase,
                    Test3 as Result
                  Resident Initial;

                   

                  drop table Initial;

                   

                   

                   

                  Regards,

                  Lester