Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thanks for the reply
I am also looking for other options other than using 'group by'
Thanks for the reply
I am also looking for other options other than using 'group by'