exclude values

[#1]

Hello all,

I can't get the excluded values from a table that contains user details!

I have table named users with ID, Name etc,

I have a reluserdata with relid, Id_user this is 1:1 with users table

and another table data that contains relid, id_value_data, description, etc. This is 1:N with  reluserdata

So i have users that have the id_value data 10 that stand for Proffession and  other that don't have yet a proffession so don't have the field 10! Almost all users have some data fileld!

When i select the id value 10 it return me 250 users from a total of 500 and it's correct but if i exclude the id_value 10 it returns me all 500 and not the remaining 250!

How can i get that?

Thanks to all and if you don't understand my bad english i'll try to explain better!

[Digvijay_Singh]

I see correct when I changed 112 to 113 in last table, second row -

[#3]

That was an example! the name association of users and reluserdata is correct.

Relid have to be linked to one or more values because 10 stand for Proffession, 15 stand for Gender  ecc

Soo how can i select the gender male that dont have a proffession?

[sebastianlettner]

Hi,

due to the 1:N relation you need to use the field relid.

Add a ListBox for relid. Now select the id_value and use 'select excluded' in the ListBox with the relid.

Regards

Sebastian Lettner

[Digvijay_Singh]

One another perspective showing the real data situation is as below -

users:

Load * inline [

userid,name,age

1,Tom, 22

2,John,24

3,Daniel, 18 ];

reluserdata:

Load * inline [

userid,relid

1,112

2,113

3,114 ];

data1:

Load * inline [

relid,id_value,desc

112,10,IT

112,15,male

113,15,male

114,15,male ];

NoConcatenate

data:

Load relid,concat(id_value,',') as id_value,concat(desc,',') as desc resident data1 group by relid;

drop Table data1;

[#6]

The solution that Digvijay  propose give me the result i want but it came with other problem! if i want to select only the male i can't! The id_value are like 30 properties that a user can have so it's complicated to concat all that!

SebastianLettner‌'s solution I've tried at first if you read the initial post and is the same that propose pokassov

This is driving me nuts ! I think i'll do a view in SQL because seems like in qlikview is not possible.

The sql will be like this:

Select * from users

where users.id NOT IN(Select userid from reluserdata inner join data on reluserdata.relid=data.relid where data.id_value=10)