topic Excluding values from table in QlikView

Excluding values from table

Thu, 11 Dec 2014 19:00:06 GMT

Hi all,

I have a requirement where i need to exclude the Orders from table depends on "Set" field value.

Let say Order 10,12 have more than one value with same Set value.So this time we need to exclude these orders from table.

INPUT:

Order	Item	Set	Material	Order Qty
10	1	1-1	ABC	112
10	2	1-1	ABC	23
11	1	1-1	XYZ	23
12	1	1-1	XCV	25
12	2	1-1	XCV	25
12	3	1-1	XCV	25
12	4	1-1	XCV	25
13	1	1-1	qac	85
14	2	1-1	wsd	25
15	1	1-1	bnd	12
100	1	2-1	dcv	45
100	2	2-2	sdf	52
101	1	2-1	klj	54
101	2	2-1	klj	25
101	3	2-2	klj	23

OUTPUT LOOKS lIKE

===============

Order	Item	Set	Material	Order Qty
11	1	1-1	XYZ	23
13	1	1-1	qac	85
14	2	1-1	wsd	25
15	1	1-1	bnd	12
100	1	2-1	dcv	45
100	2	2-2	sdf	52
101	3	2-2	klj	23 Thanks in advance

Re: Excluding values from table

arthur_dom — Thu, 11 Dec 2014 19:42:39 GMT

Hey man... I did it, please refer the attached

Re: Excluding values from table

its_anandrjs — Thu, 11 Dec 2014 19:52:54 GMT

Hi,

I am also did it with some another way also with loading only unique records and exclude the duplicate row values

Script:-

Tab1:
LOAD * INLINE [
    Orders, Item, Set,    Material, Order Qty
    10, 1, 1-1, ABC, 112
    10, 2, 1-1, ABC, 23
    11, 1, 1-1, XYZ, 23
    12, 1, 1-1, XCV, 25
    12, 2, 1-1, XCV, 25
    12, 3, 1-1, XCV, 25
    12, 4, 1-1, XCV, 25
    13, 1, 1-1, qac, 85
    14, 2, 1-1, wsd, 25
    15, 1, 1-1, bnd, 12
    100, 1, 2-1, dcv, 45
    100, 2, 2-2, sdf, 52
    101, 1, 2-1, klj, 54
    101, 2, 2-1, klj, 25
    101, 3, 2-2, klj, 23
];

left Join

LOAD
Orders,Set,
Count(Orders&Set) as OrderSetCnt
Resident Tab1 Group By Orders,Set Order By Orders,Set ;

FinalTable:
NoConcatenate
LOAD * Resident Tab1 Where OrderSetCnt=1;

DROP Table Tab1;

And result you get

Regards

Anand

Re: Excluding values from table

maxgro — Thu, 11 Dec 2014 19:56:36 GMT

if(count(TOTAL <Set, Order> Order)=1, sum([Order Qty]))

Re: Excluding values from table

its_anandrjs — Thu, 11 Dec 2014 20:55:29 GMT

Another way is load table as it as and create OrderSetCnt and in the front end write the expression

=Sum({<OrderSetCnt ={1} >} [Order Qty])

Regards

Anand

Re: Excluding values from table

Fri, 12 Dec 2014 19:05:14 GMT

Thanks all looks all your solutions perfect.

Output looks good and values are excluding from table but I have a list box with values "A" and "B".

If we make any selections in list box ,that excluded values are displaying again in table.

But I don't want to display the excluded values from table even if we make any selection.

Any suggestions ,any other solutions will help me lot.

I want to do any changes in front end only not in script level.

Thanks