Two dimension control and equal value show

[krmvacar]

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 

[vincent_ardiet_ · Accepted solution]

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;

[PrashantSangle · Accepted solution]

Try below logic

Test:

Load ORDERID, DATE, PK, FK, AMOUNT, ORDERID&'_'& DATE as Key,if(PK=FK,1,0) as flag 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

];

NoConcatenate

cnt_duplicate:

Load Key as duplicate_key,

Count(Key) as key_count

Resident Test

Group by Key;

NoConcatenate

Duplicate:

Load duplicate_key as d1 Resident cnt_duplicate where key_count>1;

NoConcatenate

Temp:

Load * Resident Test

Where not Exists(d1,Key);

Concatenate

Load * Resident Test

where Exists(d1,Key) and flag=1

;

Drop tables Test,cnt_duplicate,Duplicate;

Exit Script;

Regards,

Prashant Sangle