Remove only one Duplicate Value in Table

[Surya]

Hello.,

Here I need to remove only one duplicate as per my requirement.

[Thiago_Justen_]

What's up friend?

You can do this in some ways...here it's one:

SUM(Aggr(DISTINCT SUM(amount), KEY))

[MayilVahanan]

HI

Based on ur screenshot, for duplicate line Reason is due to "multiple" id has one Key.

If you remove the id from dimension & in expression you can try like below

Min(id)

or

Concat(Distinct Id, ',')

[Vegar]

If you can use the KEY1 to identify unique rows then you can use this script:

LOAD * 
WHERE NOT Exists (KEY1);

LOAD * inline [
KEY, 	amount, Status, 	KEY1, 	id
1163, 	100, 	Success,	1,		1
1163, 	100, 	Success,	1,		2
1163, 	100, 	Success,	1,		4
1164, 	100, 	NoSuccess,	3,		5
1263, 	100, 	Success,	2,		3
1264, 	100, 	NoSuccess,	4,		6
1365, 	300, 	NoSuccess,	5,		7
];

[Surya]

contract	date	amount	Status
1011	6/16/2021	100	Success
1011	6/16/2021	100	Success
1011	6/17/2021	200	Insufficient Blanace Type
1012	6/17/2021	100	Insufficient Blanace Type
1013	6/18/2021	300	Insufficient Blanace Type

expected output Here key is contract & date

[Surya]

Hello.,

T1:

Table A
contract No	date	Status
1011	6/16/2021	Success
1012	6/16/2021	Success
1013	6/16/2021	Success
1011	6/17/2021	Insufficient Blanace Type
1012	6/17/2021	Insufficient Blanace Type
1013	6/18/2021	Insufficient Blanace Type

T2:

Table B
contract	date	amount
1011	6/16/2021	100
1011	6/16/2021	100
1012	6/16/2021	100
1011	6/16/2021	100
1011	6/17/2021	200
1012	6/17/2021	100
1013	6/18/2021	300

expected output is:

contract	date	amount	Status
1011	6/16/2021	100	Success
1011	6/16/2021	100	Success
1011	6/17/2021	200	Insufficient Blanace Type
1012	6/17/2021	100	Insufficient Blanace Type
1013	6/18/2021	300	Insufficient Blanace Type

[Vegar]

Consider this script:

[T1]:
LOAD * inline[
contract No, date, Status
1011, 6/16/2021, Success
1012, 6/16/2021, Success
1013, 6/16/2021, Success
1011, 6/17/2021, Insufficient Blanace Type
1012, 6/17/2021, Insufficient Blanace Type
1013, 6/18/2021, Insufficient Blanace Type
];

T2:
LOAD contract as [contract No], date, amount, Hash128(contract, date) as UniqueID inline[
contract, date, amount
1011, 6/16/2021, 100
1011, 6/16/2021, 100
1012, 6/16/2021, 100
1011, 6/16/2021, 100
1011, 6/17/2021, 200
1012, 6/17/2021, 100
1013, 6/18/2021, 300]
WHERE NOT EXISTS (UniqueID, Hash128(contract, date) )
;

It can give you this output. (Notice the diff between mine and your output)

[Surya · Accepted solution]

T1:
MAPPING LOAD
"contract No" & date AS KEY,
Status
FROM [lib://50_Data/Test/SC4.xlsx]
(ooxml, embedded labels, table is t1);
//*************************************************************************//
Temp:
LOAD
"contract",date,
APPLYMAP('T1',contract&date) AS Status, //Mapping the status field
"contract"&date& APPLYMAP('T1',contract&date) AS KEY,
amount
FROM [lib://50_Data/Test/SC4.xlsx]
(ooxml, embedded labels, table is t2);
//*****************************************************************************
LOAD
*
WHERE COUNT<> 1 OR Status <> 'Success' ;
LOAD *,
IF(KEY = PEEK(KEY),PEEK(COUNT)+1,1) AS COUNT
Resident Temp
ORDER BY contract,Status;
DROP TABLE Temp;