Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello.,
Here I need to remove only one duplicate as per my requirement.
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;
What's up friend?
You can do this in some ways...here it's one:
SUM(Aggr(DISTINCT SUM(amount), KEY))
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, ',')
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
];
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
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 |
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)
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;