Reduce the duplicate-field rows based on condition

[alenb]

In the following table if the two UID's are the same I want to load it only once - the one with the condition 'isOffice=1'.

ACCOUNTS:
Load * Inline [
UID, Name, isOffice
1, A, 0
1, Office A, 1
2, B, 0
3, Office C, 1
4, Office D, 1
4, D, 0
]
;

Above should result in:

ACCOUNTS_CLEAN:
 [
UID, Name, isOffice
1, Office A, 1
2, B, 0
3, Office C, 1
4, Office D, 1
]
;

[Saravanan_Desingh · Accepted solution]

Try this,

ACCOUNTS:
Load * Inline [
UID, Name, isOffice
1, A, 0
1, Office A, 1
2, B, 0
3, Office C, 1
4, Office D, 1
4, D, 0
]
;

Left Join(ACCOUNTS)
LOAD UID, Count(UID) As Cnt
Resident ACCOUNTS
Group By UID;

Right Join(ACCOUNTS)
LOAD UID, isOffice
Resident ACCOUNTS
Where (Cnt > 1 And isOffice=1) Or Cnt=1;

Drop Field Cnt;