Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
ı have a 5 columns excel .If ORDERID and DATE fields are equal, then I check the PK and FK values. PK and FK take the equal ones and do not show the unequal ones.
If the ORDERID and DATE fields do not have equal values, there is no need to look at the PK and FK fields.
ORDERID | DATE | PK | FK | AMOUNT |
938578 | 21.02.2022 | 1 | 1 | 100 |
673947 | 21.02.2022 | 2 | 2 | 95 |
543456 | 19.03.2022 | 0 | 0 | 150 |
543456 | 19.03.2022 | 2 | 0 | 65 |
764365 | 28.11.2022 | 0 | 1 | 96 |
I want to result :
ORDERID | DATE | PK | FK | AMOUNT |
938578 | 21.02.2022 | 1 | 1 | 100 |
673947 | 21.02.2022 | 2 | 2 | 95 |
543456 | 19.03.2022 | 0 | 0 | 150 |
764365 | 28.11.2022 | 0 | 1 | 96 |
İts possible in script? thank you
This could work:
SourceTable:
Load * InLine
[ORDERID,DATE,PK,FK,AMOUNT
938578,21.02.2022,1,1,100
673947,21.02.2022,2,2,95
543456,19.03.2022,0,0,150
543456,19.03.2022,2,0,65
764365,28.11.2022,0,1,96];
mapDuplicates:
Mapping Load ORDERID&';'&DATE as KEY, Count(ORDERID&';'&DATE) as VALUE Resident SourceTable Group By ORDERID,DATE;
ResultTable:
NoConcatenate Load * Resident SourceTable
Where ApplyMap('mapDuplicates',ORDERID&';'&DATE)=1 Or PK=FK;
Drop Table SourceTable;
Try below logic
This could work:
SourceTable:
Load * InLine
[ORDERID,DATE,PK,FK,AMOUNT
938578,21.02.2022,1,1,100
673947,21.02.2022,2,2,95
543456,19.03.2022,0,0,150
543456,19.03.2022,2,0,65
764365,28.11.2022,0,1,96];
mapDuplicates:
Mapping Load ORDERID&';'&DATE as KEY, Count(ORDERID&';'&DATE) as VALUE Resident SourceTable Group By ORDERID,DATE;
ResultTable:
NoConcatenate Load * Resident SourceTable
Where ApplyMap('mapDuplicates',ORDERID&';'&DATE)=1 Or PK=FK;
Drop Table SourceTable;
Try below logic