Using a Select within a Where

[#1]

Can someone please tell me how to include a where condition which itself contains a Select? I have written the code below but the final WHERE causes a 'Garbage after statement'.

// Load the skills lookup data to be able to display descriptions rather than ID's
Skill_Groups:
LOAD [Discipline ID],
Discipline,
[Capability ID],
Capability,
[Skill ID],
Skill
FROM
[Skills Data.xls]
(biff, embedded labels, table is Sheet2$);
// Load the ratings data, although this needs to be transposed so that each row contains a single rating. Note also
// that this data contains ratings at the Skill, Capability and Discipline level also
Report_Base:
CrossTable([Skill ID ALL], Rating)
LOAD *
FROM
[Skills Data.xls]
(biff, embedded labels, header is 1 lines, table is Sheet1$);
// Filter out the ratings at Discipline and Capability level
Report:
LOAD [Skill ID ALL] as [Skill ID],
Rating
resident Report_Base
WHERE [Skill ID ALL] not in (select [Discipline ID] resident Skill_Groups);

[GabrielAraya]

Hi,
Do you test:
Report:
Load

[Skill ID All] as [Skill ID],
Reating
Resindet Report_Base
Where Not Exists( [Discipline ID], [Skill ID All]);

Gabriel

[vidyut]

Instead of using the last where clause, use the following

Where not Exists ( 'Discipline ID',[Skill ID ALL]);

That should work