4 Replies Latest reply: Apr 6, 2012 11:56 PM by Sokkorn Cheav RSS

    Cross Table

      Hi Guys

      In the attached file i have a source excel and output also  , after applying cross table i want  the output?????????

        • Cross Table
          Sokkorn Cheav

          Hi,

           

          Can you attached the sample file here? As you mention above, it hard to understand your structure.

           

          Regards,

          Sokkorn

          • Cross Table
            Yojas Samarth

            apply functions what bu want and then load it without using load * then u vl get all the records and u cn rearrange it as u want.....

            • Cross Table
              Sokkorn Cheav

              Hi,

               

              Can you add 1 or 2 records in the attached file? And then manipulate those data in your out put.

               

              Regards,

              Sokkorn

              • Re: Cross Table
                Sokkorn Cheav

                Hi,

                 

                Your data source very complicate. Let try my script:

                [D1]:
                CrossTable(StudentName,Data)
                LOAD
                    ID, 
                    StudentName, 
                    IF(ISNULL(Comments),'-',Comments) AS Comments, 
                    School, 
                    Level, 
                    School1, 
                    Level1, 
                    School2, 
                    Level2
                FROM
                [Book5.xls]
                (biff, embedded labels, header is 2 lines, table is Sheet1$);
                
                [D2]:
                LOAD
                    ID    AS [StuID],
                    PurgeChar(StudentName,'123456789')    as [Field],
                    Data    AS [Value]
                RESIDENT [D1];
                //*******************************************************************//
                [TMP1]:
                GENERIC LOAD * RESIDENT [D2];
                
                [RESULT]:
                LOAD DISTINCT [StuID] RESIDENT [D2];
                
                DROP TABLE [D2];
                
                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 TABLES TableList,D1;
                
                [Data]:
                LOAD RecNo() &'-'& StuID &'-'& School AS [Key],* RESIDENT [RESULT];
                
                DROP TABLE [RESULT];
                //**********************************************************************************//
                [D1]:
                CrossTable([2003],Data)
                LOAD 
                    Column1    AS [StuID], 
                    [2003],  
                    [2004],
                    [2005]
                FROM
                [Book5.xls]
                (biff, embedded labels, header is 1 lines, table is Sheet1$) WHERE Column1 <> 'ID';
                
                RENAME FIELD Data TO [School_Year], [2003] TO [Years];
                
                [Data2]:
                LOAD RecNo()&'-'& StuID &'-'& [School_Year] AS [Key],[Years] RESIDENT     [D1];
                
                DROP TABLE [D1];
                

                See sample attached file.

                 

                Regards,

                Sokkorn