Excluding values from table

[#1]

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

[arthur_dom]

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

[its_anandrjs]

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

[maxgro]

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

[its_anandrjs]

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

[#6]

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