Exclude exception rows - Options to handle ?

[umashankarus]

Hi,

I have an input data table as below (Note: there are tens of columns in the real data table)

ID1	CODE1	UOM1	ID2	CODE2	UOM2
A	0000002598	CASE	B	0000002598	CASE
A	0000002598	CASE	B	0000976484	EACH
A	0000003818	CASE	B	0000003818	CASE
A	0000003818	CASE	B	0000976496	EACH
A	0000023744	CASE	B	0000023744	CASE
A	0000315051	CASE	B	0000999321	EACH

If Number of occurence of CODE1  = 1, then it is an exception

Within the document, I am able to list out and display the exceptions (the last 2 rows)

ID1	CODE1	UOM1	ID2	CODE2	UOM2
A	0000023744	CASE	B	0000023744	CASE
A	0000315051	CASE	B	0000999321	EACH

But I also wanted the exceptions to be excluded and the original table (without exception records) should be displayed  as below (the first 4 rows)

ID1	CODE1	UOM1	ID2	CODE2	UOM2
A	0000002598	CASE	B	0000002598	CASE
A	0000002598	CASE	B	0000976484	EACH
A	0000003818	CASE	B	0000003818	CASE
A	0000003818	CASE	B	0000976496	EACH

Can this be handled in the load script by using 'group by' only ?

If not within the load script, can this be handled at the time of document open through trigger ?

Pl provide suggestions and details

Thanks,

Umashankar

[varshavig12]

May be something like this:,,

t1:

LOAD * INLINE [

    ID1, CODE1, UOM1, ID2, CODE2, UOM2

    A, 0000002598, CASE, B, 0000002598, CASE

    A, 0000002598, CASE, B, 0000976484, EACH

    A, 0000003818, CASE, B, 0000003818, CASE

    A, 0000003818, CASE, B, 0000976496, EACH

    A, 0000023744, CASE, B, 0000023744, CASE

    A, 0000315051, CASE, B, 0000999321, EACH

];

t2:

left join load  CODE1,count(ID1) as c

Resident t1

group by CODE1;

right join load *

Resident t1

where c>1;

[Kushal_Chawda]

t1:

LOAD * INLINE [

    ID1, CODE1, UOM1, ID2, CODE2, UOM2

    A, 0000002598, CASE, B, 0000002598, CASE

    A, 0000002598, CASE, B, 0000976484, EACH

    A, 0000003818, CASE, B, 0000003818, CASE

    A, 0000003818, CASE, B, 0000976496, EACH

    A, 0000023744, CASE, B, 0000023744, CASE

    A, 0000315051, CASE, B, 0000999321, EACH

];

t2:

LOAD ID1,

         CODE1,

         Count(CODE1) as Count,

         UOM1,

         ID2,

         CODE2,

         UOM2

resident t1

group by

ID1,

UOM1,

ID2,

CODE2,

UOM2;

drop table t1;

t3:

noconcatenate

LOAD *

resident t2

where Count>1;

drop table t2;

[umashankarus]

Thanks for the reply

I am also looking for other options other than using 'group by'

[umashankarus]

Thanks for the reply

I am also looking for other options other than using 'group by'