Script Load - Quit duplicated rows

[LDR]

Hi everyone,

Thanks in advance for your support.

I have one table with duplicated KEYs (Original) and I'd like to obtain a final table where KEY values won't be duplicated. In order to decide what KEY keep in the final result I want to exclude duplicated KEYs where Flag column is equal to 'A'.

Original:
Load * Inline [
CostCenter,ValidFrom,Flag,Qty,KEY
1001001,01042021,,4,1001001-01042021
1001001,01042021,A,,1001001-01042021
1001001,01052021,,4,1001001-01052021
1001001,01052021,A,,1001001-01052021
1001001,01062021,A,,1001001-01062021
];

Temp_A:
NoConcatenate
LOAD *
RESIDENT Original
WHERE Flag <> 'A'
;
LOAD *
RESIDENT Original
WHERE Flag = 'A' AND NOT Exists(KEY);

DROP TABLE Original;

The result that I'm obtaining is this one:

KEY	CostCenter	Flag	ValidFrom	Qty
1001001-01042021	1001001		01042021	4
1001001-01052021	1001001		01052021	4

But the one I'd like is the result below:

KEY	CostCenter	Flag	ValidFrom	Qty
1001001-01042021	1001001		01042021	4
1001001-01052021	1001001		01052021	4
1001001-01062021	1001001	A	01062021

So, can anybody tell me what I have to do to obtain the aimed result?

Regards

[Kushal_Chawda]

@LDR  try below

Original:
Load * Inline [
CostCenter,ValidFrom,Flag,Qty,KEY
1001001,01042021,,4,1001001-01042021
1001001,01042021,A,,1001001-01042021
1001001,01052021,,4,1001001-01052021
1001001,01052021,A,,1001001-01052021
1001001,01062021,A,,1001001-01062021
];

Left Join(Original)
LOAD KEY,
     count(KEY)as cnt_key
Resident Original
Group by KEY;

Final:
NoConcatenate
LOAD *
Resident Original
Where cnt_key=1;

Concatenate(Final)
LOAD *
Resident Original
Where cnt_key>1 and Flag<>'A';

DROP Table Original;

[Vegar · Accepted solution]

The reason you don't get the desired row is because the KEY exists in the Original table at the time of execution. 

To avoid this try the following.

Original:
Load * Inline [
CostCenter,ValidFrom,Flag,Qty,KEY
1001001,01042021,,4,1001001-01042021
1001001,01042021,A,,1001001-01042021
1001001,01052021,,4,1001001-01052021
1001001,01052021,A,,1001001-01052021
1001001,01062021,A,,1001001-01062021
];

Temp_A:
NoConcatenate
LOAD *, KEY AS tempKEY
RESIDENT Original
WHERE Flag <> 'A'
;
LOAD *, KEY AS tempKEY
RESIDENT Original
WHERE Flag = 'A' AND NOT Exists(tempKEY, KEY);

DROP TABLE Original;

DROP FIELD tempKEY;