Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Where Exists Question

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:

idCCUser
1JOHNDOE
2JOHNDOE
3JOHNDOE
4JANEDOE
5JANEDOE

But I'm actually getting the following:

idCCUser
1JOHNDOE
2JOHNDOE
3JOHNDOE

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

1 Solution

Accepted Solutions

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;

View solution in original post

2 Replies

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;

View solution in original post

Contributor III
Contributor III

Thank you, this worked!