Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
You want to do this in script or only for a table?
Hi,
Anything is fine for me. If possible give the solution in both ways.
Thanks
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;
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]
;