topic Re: Remove only one Duplicate Value in Table in App Development

Remove only one Duplicate Value in Table

Surya — Tue, 30 Nov 2021 16:25:32 GMT

Hello.,

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

Re: Remove only one Duplicate Value in Table

Thiago_Justen_ — Thu, 02 Sep 2021 06:55:46 GMT

What's up friend?

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

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

Re: Remove only one Duplicate Value in Table

MayilVahanan — Thu, 02 Sep 2021 06:56:01 GMT

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)

Concat(Distinct Id, ',')

Re: Remove only one Duplicate Value in Table

Vegar — Thu, 02 Sep 2021 06:56:29 GMT

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

Re: Remove only one Duplicate Value in Table

Surya — Thu, 02 Sep 2021 06:59:47 GMT

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

Re: Remove only one Duplicate Value in Table

Surya — Thu, 02 Sep 2021 07:08:44 GMT

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

Re: Remove only one Duplicate Value in Table

Vegar — Thu, 02 Sep 2021 15:01:10 GMT

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)

Re: Remove only one Duplicate Value in Table

Surya — Fri, 03 Sep 2021 10:40:29 GMT

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;