topic Re: Script Load - Quit duplicated rows in App Development

Script Load - Quit duplicated rows

LDR — Tue, 31 Aug 2021 11:38:53 GMT

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

Re: Script Load - Quit duplicated rows

Kushal_Chawda — Tue, 31 Aug 2021 14:51:39 GMT

@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;

Re: Script Load - Quit duplicated rows

Vegar — Tue, 31 Aug 2021 16:45:56 GMT

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.

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;