Where Exists Question

[Anonymous]

Hi all, I have the following data table which I'm trying to consolidate using Where Not Exists. Looked around and couldn't find something to fix this so if anyone has suggestions, please let me know. Thanks.

MgmtBase:
LOAD * INLINE [
    idCC, User, Type
    1, JOHNDOE, OWNER
    2, JOHNDOE, OWNER
    3, JOHNDOE, OWNER
    1, JANEDOE, BACKUP
    2, JANEDOE, BACKUP
    3, JANEDOE, BACKUP
    4, JANEDOE, BACKUP
    5, JANEDOE, BACKUP
];

MgmtMap:
NoConcatenate
LOAD idCC,
User
Resident MgmtBase
Where Type = 'OWNER';

Concatenate(MgmtMap)
LOAD idCC,
User
Resident MgmtBase
Where Type = 'BACKUP' AND Not Exists (idCC);

DROP Table MgmtBase;

I'm expecting an output:

idCC	User
1	JOHNDOE
2	JOHNDOE
3	JOHNDOE
4	JANEDOE
5	JANEDOE

But I'm actually getting the following:

idCC	User
1	JOHNDOE
2	JOHNDOE
3	JOHNDOE

For some reason I can't get it to work. Any ideas why this is happening?

[sunny_talwar · Accepted solution]

Try this

MgmtBase: 

LOAD * INLINE [ 

    idCC, User, Type 

    1, JOHNDOE, OWNER 

    2, JOHNDOE, OWNER 

    3, JOHNDOE, OWNER 

    1, JANEDOE, BACKUP 

    2, JANEDOE, BACKUP 

    3, JANEDOE, BACKUP 

    4, JANEDOE, BACKUP 

    5, JANEDOE, BACKUP 

]; 

MgmtMap: 

NoConcatenate 

LOAD idCC as idCCTemp, 

User, 

Type 

Resident MgmtBase 

Where Type = 'OWNER'; 

Concatenate(MgmtMap) 

LOAD idCC as idCCTemp, 

User, 

Type 

Resident MgmtBase 

Where Type = 'BACKUP' AND Not Exists (idCCTemp, idCC); 

DROP Table MgmtBase;

RENAME Field idCCTemp to idCC;

[Anonymous]

Thank you, this worked!