Script Editor - Exclude duplicated rows filtering by column

[LDR]

Hi everyone,

First at all thanks in advance for your time 😉

In summary:

- I have one table with duplicated rows and I want to obtain one with one row by KEY column.

Let me explain myselft with an example:

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

And the result I'd like to obtain would be:

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

Can anybody explain me how to obtain the desired result?

Thanks

[Digvijay_Singh · Accepted solution]

May be try this - 

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 KEY_TEMP
RESIDENT Original
WHERE Flag <> 'A'
;
Concatenate
LOAD *
RESIDENT Original
WHERE Flag = 'A' AND NOT Exists(KEY_TEMP,KEY);

DROP TABLE Original;

[stevejoyce]

Sorry - looks like @Digvijay_Singh had same solution.  I would just add the new aliased key field in your concatenate statement like I had below, otherwise the <> A records will not have a Key value in the data model.

See below.  your where not exists is not going to work because it checks for values not loaded in memory yet, and all key values have been loaded with your Original load, so I aliased it for first load.  And then concatenate with where not exists.  Also your preceeding load, you are doing a sub-select to what was loaded before it.  These need to be 2 separate load statements and combined with concatenate.

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

//First low distinct Keys where Flag <> A
Temp_A:
NoConcatenate
LOAD Distinct
*
,KEY as KEY_new
RESIDENT Original
WHERE Flag <> 'A'
;

Concatenate(Temp_A)
Load Distinct
*
,KEY as KEY_new
Resident Original
Where NOT EXISTS(KEY_new, KEY);

//cleanup

Drop table Original;
Drop field KEY;

Rename field KEY_new to KEY;

exit script;

[LDR]

Thanks it worked

So my issue was because I was using KEY column firstly loaded in the Original table thus I should have created a new column in order to load it and be able to compare it. Umm ...I have to learn a lot 😉

[LDR]

@stevejoyce thanks for your contribution it also was useful 😉