How to remove the single values in the table

[venkatbza]

Hi Friends,

I have a table which contains two columns order number and line item number. Each order number can contain multiple line items numbers and few are having single line items number also. Here my requirement is i have to remove if order number having single line item number from the table. below is the sample data.

Sample data:

Order number	Line item number
10001	1
10001	2
10001	3
10002	1
10002	2
10002	3
10002	4
10003	1
10004	1
10005	1
10005	2
10006	1

Output expected:

Order number	Line item number
10001	1
10001	2
10001	3
10002	1
10002	2
10002	3
10002	4
10005	1
10005	2

[dplr-rn]

You want to do this in script or only for a table?

[venkatbza]

Hi,

Anything is fine for me. If possible give the solution in both ways.

Thanks

[asinha1991 · Accepted solution]

A:

Load [Order number] where cnt >1;

Load [Order number],  count(distinct Line item number) as cnt from urtable group by  [Order number];

inner join(A)

Load * from urtable;

[dplr-rn]

i see asinhya has give script to remove it during load.

use below approach to flag it so that you do it in Ui as well. (where MoreThan1LineItemFlag=1)

Orders:
load * inline [

Order number,	Line item number
10001,	1
10001,	2
10001,	3
10002,	1
10002,	2
10002,	3
10002,	4
10003,	1
10004,	1
10005,	1
10005,	2
10006,	1
];

Count:
load [Order number],
 1 as MoreThan1LineItemFlag
 where Count>1
;
load [Order number]
,count([Line item number]) as Count
resident Orders
group by [Order number]
;