topic Re: exclude values in QlikView

exclude values

Fri, 09 Oct 2015 09:13:56 GMT

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!

Re: exclude values

pokassov — Fri, 09 Oct 2015 11:27:12 GMT

count({$<id=-{10}>} Name)

Re: exclude values

pokassov — Fri, 09 Oct 2015 11:28:22 GMT

t1:

LOAD * Inline [

T1, T2, T3

1, 10, 1

2, 5, 1,

3, 8

];

first expression shows 15:

=sum({$<T3={1}>}T2)

second expression shows 8:

=sum({$<T3=-{1}>}T2)

Re: exclude values

Fri, 09 Oct 2015 11:51:31 GMT

I dont need to sum or count them! i need to view all the user data in a table, so if i select the id_value 10 it show me only the users that have in the table data(that is many to 1 user) id_value 10. How can i do the same for showing only those who dont have id_value=10 becouse now if i made and excluded selection it show me all of them because an user can have many id_value.

EXample:

users:

id,name,age

1,Tom, 22

2,John,24

3,Daniel, 18

reluserdata:

userid,relid

1,112

2,113

3,114

data:

relid,id_value,desc

112,10,IT

112,15,male

113,15,male

114,15,male

so if i select the user who have id value 10 it gives me Tom so only one

But if i select exclude 10 it gives me all of the beacouse id_value has data!

how can i made to show me only the rest of them: John and Daniel

Re: exclude values

pokassov — Fri, 09 Oct 2015 14:03:05 GMT

1. add listbox with id_value

2. select 10

3. right mouse click and select excluded?

Re: exclude values

Fri, 09 Oct 2015 14:34:30 GMT

Don't work, same result!

I can do it in sql but i need something similar in qw

something like that:

SELECT u.* FROM Users u WHERE u.id NOT IN ( SELECT d.user_id FROM Documents d WHERE d.value_id =10 )

Re: exclude values

pokassov — Mon, 12 Oct 2015 06:36:32 GMT

Hi! Add your qvw file.

Re: exclude values

Thu, 15 Oct 2015 12:58:58 GMT

I can't! it contains user data and other sensible data!

I wrote you the sql query! In qlikview is it possible to do that? to exclude fields of a previous select?

Re: exclude values

pokassov — Thu, 15 Oct 2015 14:14:34 GMT

Create sample document.

Re: exclude values

Digvijay_Singh — Thu, 15 Oct 2015 14:52:12 GMT

Hi,

Your users and reluserdata are not showing same field name for association, I think id of first table should be 'userid' or other way round.

Also relid 112 is linked to both 10 and 15 id value in last table, thats why it is picked for both 10 and 15. Because both are linked to ID value 1 or 'Tom'.

Re: exclude values

Digvijay_Singh — Thu, 15 Oct 2015 14:55:40 GMT

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

Re: exclude values

Thu, 15 Oct 2015 15:13:03 GMT

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?

Re: exclude values

sebastianlettner — Thu, 15 Oct 2015 15:33:17 GMT

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

Re: exclude values

Digvijay_Singh — Thu, 15 Oct 2015 19:38:21 GMT

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;

Re: exclude values

Fri, 16 Oct 2015 06:29:10 GMT

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)