topic Resident Load Problem in QlikView

Resident Load Problem

Fri, 15 May 2009 20:06:14 GMT

Hi *,

I have two tables and querry problem. I'm loading via RESIDENT:

Users:

User_id, User_Name,...
1, QWE
2, RTZ
3, UIO
4, PAS

Adresses:
Adress_id, User_id, ...
1,1
2,2
4,4

So User 3 has no Adress.
I'd like to Resident Load only the the Users that don't have an adress like User 3.

Thanks.

disqr_rm — Fri, 15 May 2009 20:18:15 GMT

There are many ways to do that. One way I could quickly think is:

Users:
LOAD * INLINE [
User_id, User_Name
1, QWE
2, RTZ
3, UIO
4, PAS
];

Adresses:
LOAD * INLINE [
Adress_id, User_id
1, 1
2, 2
4, 4
];

MapAddr:
MAPPING
LOAD DISTINCT
User_id as id,
Adress_id as ai
resident Adresses;

NoAddress:
LOAD
User_id, aid,
1 as NoAddressFlag
where len(aid) = 0;
LOAD
User_id,
applymap('MapAddr', User_id, null()) as aid
resident Users;

rbecher — Fri, 15 May 2009 20:55:18 GMT

I would try this:

NoAddress:
LOAD
Users.User_id
RESIDENT Users WHERE not exists(Adresses.User_id, Users.User_id);

...will only work with qualified tables

yblake — Sat, 16 May 2009 17:17:23 GMT

an easy way to go :

left join (Users) load User_id, count(Address_id) as Nb_address resident Adresses group by User_id;

You will know definitely who has no address with isnull(Nb_address), and who has more than one.