Selection of line item according to the condition

[ruma_barman]

Hi All,

The requirement is : we have an asset id and we have corresponding cwip ids, asset id and cwip can be same and different, one asset id can have multiple cwip id, from these multiple cwip ids, if there is only one occurrence of cwip id  it will be equal to asset id, else in case of multiple cwip ids it can be different plus one same cwip id which equals asset id, like below example. 

what we need is in case of multiple occurrence we don't need same asset id and cwip id , but in case of single occurrence we need asset id which equals cwip id.

Input :

assetid , cwipid, Amount
a1, a1, 100    
a1, b1, 200    
a2, a2, 300
a2, b1, 400
a2, b2, 500
a3, a3, 600
a4, a4, 700
a4, b4, 600
a5, a5, 500

Output:

assetid , cwipid, Amount
a1, b1, 200
a2, b1, 400
a2, b2, 500
a3, a3, 600
a4, b4, 600
a5, a5, 500

Thanks in advance!

[lironbaram · Accepted solution]

Hi 

if you want to do it in the script , 

you can use below script.

Table1:
Load * inline
[
assetid , cwipid, Amount
a1, a1, 100
a1, b1, 200
a2, a2, 300
a2, b1, 400
a2, b2, 500
a3, a3, 600
a4, a4, 700
a4, b4, 600
a5, a5, 500
];
Left join (Table1)
Load assetid,
count(DISTINCT cwipid) as counter
Resident Table1
group by assetid;

Table2:
NoConcatenate load *
Resident Table1
where counter=1 or (assetid<>cwipid);

drop Table Table1;

if you want to do it in the front end look at the attach example 

[ruma_barman]

Thank you Lironbaram.