topic Re: Filter Out Lowest Record - Advice Needed in QlikView

Filter Out Lowest Record - Advice Needed

haymarketpaul — Thu, 28 May 2015 11:51:02 GMT

I have data in a table like this.

Within the load script I need to filter out or flag the lowest UserID where their GMC#s match. I need to keep the records that have no UserID as well.

Rec#	UserID	CompID	GMC#
1	123	456	123456
2	789	456	123456
3		987	234567
4		1234	345678
5	321		987654
6	987		987654

So from the above data i would end up with Rec#s 2,3,4,6 with Rec# 1 & 5 either filtered out or flagged

I was trying to use an approach like the one below but i end up losing the records with no UserID

Inner Join (tbl1)

LOAD

GMC#

,Max(UserID) as UserID

Resident tbl1

Group By GMC#;

Any Ideas how best to approach this much appreciated

Re: Filter Out Lowest Record - Advice Needed

swuehl — Thu, 28 May 2015 11:56:34 GMT

Try like this to flag your records:

T1:

LOAD * FROM YourTable;

LEFT JOIN

LOAD

min(UserID) as UserID,

[GMC#],

1 as Flag

RESIDENT T1

GROUP BY [GMC#];

Re: Filter Out Lowest Record - Advice Needed

haymarketpaul — Thu, 28 May 2015 12:38:39 GMT

Thanks for reply - that works although i've now realised my data is more complex than i thought