topic Re: CrossTable Orphan Issue in QlikView

CrossTable Orphan Issue

Tue, 09 Oct 2012 15:24:11 GMT

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 
(ooxml, embedded labels, header is 0 lines, table is Sheet1);

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

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

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

Tue, 09 Oct 2012 15:32:10 GMT

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

(ooxml, embedded labels, table is Sheet1);

CD:

CrossTable(Attr1, [CourseCode],1)

Load *

Resident CourseDelivery

where 1 = 1;

drop table CourseDelivery;

Re: CrossTable Orphan Issue

Anonymous — Mon, 23 Feb 2015 00:34:41 GMT

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;