Task

[Surya]

Hello., Question: We have two tables Table A and Table B. Table A has unique records and Table B is a transaction table and has redundent data.

We have to remove all those records from transaction table B that has status success in Table A but in case of muliple same records we have eliminate only one record from those multiple records.

Table1:

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

Table2:

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:

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

Regards,

Surya 

[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 SNO <> 1 OR Status <> 'Success' ;
LOAD *,
IF(KEY = PEEK(KEY),PEEK(COUNT)+1,1) AS COUNT
Resident Temp
ORDER BY contract,Status;
DROP TABLE Temp;