2 Replies Latest reply: Feb 22, 2015 7:34 PM by Walter Tello RSS

    CrossTable Orphan Issue

      I've seen a number of posts where people have had $orphans created as a result of using the CrossTable function. I'm trying to apply the function, then have a reference to the columns with the table. When I use the following script, I get a nice table:

       

      CourseDelivery:
      CrossTable(RequirementNumber,CourseCode)
      LOAD 
                [Course Name],
                [Requirement 1],
                [Requirement 2],
                [Requirement 3],
                [Requirement 4],
                [Requirement 5],
                [Requirement 6]
      FROM [D:\Delivery.xlsx]
      (ooxml, embedded labels, header is 0 lines, table is Sheet1);
      

       

      yay.png

       

      When I introduce the qualify/unqualify statement, I get the following representation of my table:

       

      QUALIFY *;
      UNQUALIFY EmployeeId, PositionID, TrainingType, CourseCode;
      

       

      boo.png

       

      What can I do to avoid the orphan? I can't get my head around why this would happen and it's ruining the rest of the load of data due to circular load issues. For a change, I can supply the spreadsheet as it isn't sensitive data (see attached).

       

      Any help is, as always, appreciated. Cheers!

        • Re: CrossTable Orphan Issue

          I might've solved it, but it isn't very pretty:

           

          QUALIFY *;
          UNQUALIFY EmployeeId, PositionID, TrainingType, CourseCode;

           

          CourseDelivery:

          LOAD [Course Name] as CourseName,

               [Requirement 1],

               [Requirement 2],

               [Requirement 3],

               [Requirement 4],

               [Requirement 5],

               [Requirement 6]

          FROM

          [D:\Delivery.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          CD:

          CrossTable(Attr1, [CourseCode],1)

          Load *

          Resident CourseDelivery

          where 1 = 1;

           

           

          drop table CourseDelivery;

            • Re: CrossTable Orphan Issue
              Walter Tello

              Could be so treated:

              Directory;

              TempTest:

              CrossTable([Requirement ], CodCurso)

              LOAD

                   [Course Name],

                   [Requirement 1],

                   [Requirement 2],

                   [Requirement 3],

                   [Requirement 4],

                   [Requirement 5],

                   [Requirement 6]

              FROM

              Delivery.xlsx

              (ooxml, embedded labels, table is Sheet1);

               

              QUALIFY *;

              UNQUALIFY EmployeeId, PositionID, TrainingType, CourseCode;

               

              Test:

              Load *

              Resident TempTest;

              drop table TempTest;