topic Re: Load script keep unique based on condition/hierarchy in QlikView

Load script keep unique based on condition/hierarchy

samuel_lin — Thu, 05 Oct 2017 22:24:00 GMT

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

Re: Load script keep unique based on condition/hierarchy

kenphamvn — Fri, 06 Oct 2017 01:51:59 GMT

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

Re: Load script keep unique based on condition/hierarchy

samuel_lin — Fri, 06 Oct 2017 02:42:34 GMT

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

Re: Load script keep unique based on condition/hierarchy

kenphamvn — Fri, 06 Oct 2017 02:56:45 GMT

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

Re: Load script keep unique based on condition/hierarchy

samuel_lin — Fri, 06 Oct 2017 03:18:48 GMT

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

SourceD>SourceA>SourceC>SourceK

Re: Load script keep unique based on condition/hierarchy

kenphamvn — Fri, 06 Oct 2017 04:28:24 GMT

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