Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
we have data as follows :
PROBLEM:
Date | EquipmentID | CounterNo | Coulmn 4 | Column 5 |
9092022 | D123 | 100 | ||
9092022 | D124 | 100 | ||
9092022 | D126 | 101 | ||
9092022 | D126 | 101 | ||
9092022 | D129 | 102 | ||
9092022 | D130 | 103 | ||
9092022 | D131 | 104 | ||
9092022 | D131 | 104 | ||
9092022 | D132 | 105 | ||
9092022 | D134 | 105 |
In the above data set some of the counterNo are Repeating, i want to extract the rows having duplicate Counter No but different EquipmentID, so the result i want is :
RESULT SHOULD BE:
Date | EquipmentID | CounterNo | Coulmn 4 | Column 5 |
9092022 | D123 | 100 | ||
9092022 | D124 | 100 | ||
9092022 | D132 | 105 | ||
9092022 | D134 | 105 |
I hope i explain the problem properly.
Can anyone help me please.
Looking forward for quick response plaese 🙂
Thnakyouuuuuuuuuuuuu in ADVANCE
hi,
you will get the output
data:
Load *,
'-'&CounterNo as key;
load * Inline [
Date,EquipmentID,CounterNo
9092022,D123,100
9092022,D124,100
9092022,D126,101
9092022,D126,101
9092022,D129,102
9092022,D130,103
9092022,D131,104
9092022,D131,104
9092022,D132,105
9092022,D134,105
];
inner Join(data)
load
key,
count_EquipmentID
Where count_EquipmentID>1;
load
key,
Count(EquipmentID) as count_EquipmentID
Group by key;
load EquipmentID,
'-'&CounterNo as key,
CounterNo,
12 as j
Resident data;
/*
output:
Date EquipmentID CounterNo
9092022,D123,100
9092022,D124,100
9092022,D132,105
9092022,D134,105
*/
Hi @ajaykakkar93 Thankyou so Much for the Reply. Actually the script you used is little confusing for me, as some of my concepts are not clear, sorry for that. If possible can you please explain the working of this script. it would be a great help.
Again Thank you for the Reply 🙂
@ajaykakkar93 Yes.