topic Re: Where Exists Question in QlikView

Where Exists Question

Anonymous — Mon, 15 Jan 2018 17:57:58 GMT

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?

Re: Where Exists Question

sunny_talwar — Mon, 15 Jan 2018 18:02:01 GMT

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;

Re: Where Exists Question

Anonymous — Mon, 15 Jan 2018 18:04:19 GMT

Thank you, this worked!