Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
alenb
Partner - Contributor III
Partner - Contributor III

Reduce the duplicate-field rows based on condition

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
]
;

 

1 Solution

Accepted Solutions
Saravanan_Desingh

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;

commQV23.PNG

View solution in original post

1 Reply
Saravanan_Desingh

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;

commQV23.PNG