Load script keep unique based on condition/hierarchy

[samuel_lin]

Hi Smarties

Say I have a table in Load script:

ID  Source

123   SourceA

123   SourceC

123   SourceK

234   SourceA

234   SourceC

456   SourceD

there may be multiple Sources for each ID. I want to have a table with just one Source per ID based on a condition that:

SourceA>SourceD>SourceC>SourceK (an example). Any thought about a way to do this? I was thinking to assign a number to each source and then somehow remove the lower ranked source for each ID, but am stuck. Please let me know if you have any ideas.

Thanks,

Samuel

[kenphamvn]

Try this,

Rawtable:
LOAD * inline [
ID,Source
123,SourceA
123,SourceC
123,SourceK
234,SourceA
234,SourceC
456,SourceD
];

NoConcatenate
FinalTable:
load ID,Source
Resident Rawtable
where AutoNumber(ID&Source,ID)=1;

drop Table Rawtable;

Regards,

An Pham

[samuel_lin]

Hi An,

Thank you for the reply. I like the autoNumber idea. Just so I have the clear understanding, does Rawtable have to be pre-sorted in a certain way for autonumber to work? Or does autoNumber basically assign 1 to A, 2 to B and so forth (regardless of the row order)

Thanks,

Samuel

[kenphamvn]

Hi

You don't need to pre-sorted,  Autonumber will assign 1 to first value found, 2 for second value (Same ID but others table)....

Regards

An Pham

[samuel_lin]

What would you suggest if I want to achieve something in this order?

SourceD>SourceA>SourceC>SourceK

[kenphamvn]

Hi

another way with tricks

//Data Source
Rawtable:
LOAD * inline [
ID,Source, 
123,SourceK
123,SourceD
234,SourceA
234,SourceC
456,SourceA
123,SourceC
234,SourceD
765,SourceF
765,SourceA
];

//Define Order
OrderTable:
load ID&FirstSortedValue(Source,
pick(match(Source,'SourceD','SourceA','SourceC','SourceK'),1,2,3,4) 
) as ListKeySource
Resident Rawtable
group by ID;


//Get final Data
NoConcatenate
FinalTable:
load *
Resident Rawtable
where Exists(ListKeySource,ID&Source);


Drop Table OrderTable;
drop Table Rawtable;

Regards

An Pham