Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jhillock
New Contributor II

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?

Tags (1)
1 Solution

Accepted Solutions

Re: Where Exists Question

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;

2 Replies

Re: Where Exists Question

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;

jhillock
New Contributor II

Re: Where Exists Question

Thank you, this worked!

Community Browser