Filter the values

[#1]

Hi All,

I am Using Qlikview10.

Please refer the following table:

M.Contacts	D.Contacts	M.Account	D.Account
1	1	23	23
2	-	25	-
3	3	27	-
4	4	30	30
5	-	35	-
6	6	40	-
7	7	45	-
8	8	50	50
9	-	-	-
10	10	-	-
11	-	-	-
12	-	-	-
13	-	-	-

I want this table should view as following:

M.Contacts	D.Contacts	M.Accounts	D.Accounts
9	-	-	-
10	10	-	-
11	-	-	-
12	-	-	-
13	-	-

It means I want to see the records where the M.Accounts and D.Accounts are Null against the Contacts.

How to Filter it?

Thanks,

Sandeepa

[#2 · Accepted solution]

Hi

i sugges to use a straight table like in the attached example

Regards

Gilles

[SunilChauhan]

see the attacheed filee

but i hope you need to do like below

Tab:
load * inline [
M.Contacts,D.Contacts,M.Account,D.Account
1, 1 ,23, 23
2, -, 25, -
3, 3 ,27 ,-
4 ,4 ,30, 30
5 ,-, 35, -
6 ,6 ,40, -
7 ,7 ,45 ,-
8 ,8 ,50, 50
9 ,- ,- ,-
10 ,10, - ,-
11 ,-, -, -
12, - ,-, -
13 ,-, - ,-
];

load * ,
1 as junk
resident Tab where isnull(M.Account)=-1 and isnull(D.Account)=-1;

drop table Tab;

or
Tab:
load * inline [
M.Contacts,D.Contacts,M.Account,D.Account
1, 1 ,23, 23
2, -, 25, -
3, 3 ,27 ,-
4 ,4 ,30, 30
5 ,-, 35, -
6 ,6 ,40, -
7 ,7 ,45 ,-
8 ,8 ,50, 50
9 ,- ,- ,-
10 ,10, - ,-
11 ,-, -, -
12, - ,-, -
13 ,-, - ,-
];

load * ,
1 as junk
resident Tab where isnull(M.Account) and isnull(D.Account);

drop table Tab;

or
Tab:
load * inline [
M.Contacts,D.Contacts,M.Account,D.Account
1, 1 ,23, 23
2, -, 25, -
3, 3 ,27 ,-
4 ,4 ,30, 30
5 ,-, 35, -
6 ,6 ,40, -
7 ,7 ,45 ,-
8 ,8 ,50, 50
9 ,- ,- ,-
10 ,10, - ,-
11 ,-, -, -
12, - ,-, -
13 ,-, - ,-
];

load * ,
1 as junk
resident Tab where len(M.Account)=0 and len(D.Account)=0;

drop table Tab;

[#4]

Thanks Gilles.. It works...

Your effort is really appreciable.

Many Thanks,

Sandeepa

[#5]

Thanks Sunil...!

It does work.

But it will be more easier if I use it in table level like Gilles suggested.

Thanks for the response...!