topic sorting then removing duplicates in QlikView

sorting then removing duplicates

Mon, 20 Feb 2017 21:54:54 GMT

When loading the data I need to keep only the latest record.

I don't have a date to rely on to determine the latest record but I have a status, based on which I can sort in a somewhat chronological order.

I read that Order By can only be used on Resident Loads and that removing duplicates was achieved by joining the data with itself.

The sorting by Status works fine.

but it seems I can't join a resident load with itself

[Raw]:

LOAD

...

[STATUS],

If([STATUS]='Canceled', '1', If([STATUS]='Closed', '2', If([STATUS]='Transmited', '3',))) as 'Status ID',

...

FROM [....]

[Ordered]:

Load *

Resident [Raw] Order By [Status ID];

Join ([Ordered])

[Unique]

Load *

Resident [Ordered]

Group By [Status ID];

Re: sorting then removing duplicates

rwunderlich — Mon, 20 Feb 2017 22:40:52 GMT

I assume you have an ID field that identifies each "thing" (case, order whatever) that you want to keep. Maybe you are looking for something like this:

Raw:

LOAD *,

Match(Status,'Cancelled','Closed','Transmitted') as StatusID

INLINE [

ID, Status, Foo

1, Cancelled, A1

1, Closed, A2

2, Transmitted, Z1

3, Closed, Y1

3, Transmitted, Y2

];

INNER JOIN (Raw)

LOAD

ID,

min(StatusID) as StatusID

Resident Raw

Group by ID;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: sorting then removing duplicates

Tue, 21 Feb 2017 17:14:31 GMT

Thanks Rob!

I like the min(StatusID) trick, pretty easier than my sorting then filtering...

I like the use of match() to associate numbers/id to strings too. In my case I actually need a default case but because match() returns 0 when no match is found it still works fine, I just reverted the order of the various statuses, and used max() instead of min().

Great tricks, thanks for your help