Removing duplicate records

sagar_vij99 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am trying to load data with some preceding load fields. this is working fine but when i use it again to remove duplicate records records based on single column values. it is not working properly.

What i need is duplicate records should be moved based upon the values in ConcatField column only. With my current code it is removing duplicates by grouping based on column Risk Number.

Here is the code:

Peer_Review:
load *, if(Not IsNull([Pre-Bind Peer Review Date])or not IsNull([BGL Approval Date])or not IsNull([Pre-Bind Approval Date]) or not isnull([Pre-Bind Peer Review Note]) or not IsNull([Retrospective Peer Review Exempt])
or not IsNull([CUO pre-bind approval Date]) or not IsNull([Quote Peer Review Date]), 'Reviewed','Not-Reviewd') as Review_Check,
month(IncptDate) as MonthD,
year(IncptDate) as YearD,
MonthEnd(IncptDate) as InceptnDateEnd,
MonthEnd([Pre-Bind Peer Review Date]) as PBPRDate,
MonthEnd([BGL Approval Date]) as BGLDate,
MonthEnd([Pre-Bind Approval Date]) as PBADate,
MonthEnd([Pre-Bind Peer Review Note]) as PBPNDate,
MonthEnd([Retrospective Peer Review Exempt]) as RPREDate,
MonthEnd([CUO pre-bind approval Date]) as CPBADate,
MonthEnd([Quote Peer Review Date]) as QPRDate,
[Risk Number]&'/'&DeclnNmbr as ConcatField;

LOAD [Risk Number],
BusinessGroup,
[EDW Hub Name],
[Accounting Behaviour Code],
CostCentre,
OrigOffName,
UwtgYearAcct,
RiskCode,
[Insured Else Reinsured],
UWName,
UwtgRef,
IncptDate,
[Incpt Month],
[Pre-Bind Peer Review Date],
[BGL Approval Date],
[Pre-Bind Approval Date],
[Pre-Bind Peer Review Note],
[Post Bind Peer Review Date],
[Retrospective Peer Review Exempt],
[CUO pre-bind approval Date],
[Quote Peer Review Date],
PromisedDate,
QuotedDate,
WrtnDate,
[Risk Policy Type],
[Section Policy Type],
[Policy Type],
NewRenwl,
[Policy Duration],
SgndStatus,
DeclnNmbr,
[Conv NA WEPI],
[Conv LimitExposure],
ActLine,
RnwlPcntChnge,
[Expected ULR],
PremAdeqPcnt
FROM
[C:\Users\A110506\Desktop\Top Hat\Peer Review\Cognos report peer review.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Report 1_2])
where WildMatch(CostCentre,'FX','FE','FC','NB','NE','NN','NR','NF','CE','CU','CL','CW')
and WildMatch(OrigOffName,'CUAL/CICL LONDON','CATLIN BIRMINGHAM','CRSL LONDON')
and WildMatch([Incpt Month],'Jan','Feb','Mar');

//Removing duplicates
Peer_Review_Inter:
//load *, AutoNumber(ConcatField) as ID;
load distinct ConcatField,
[Review_Check],MonthD,YearD,InceptnDateEnd,PBPRDate,BGLDate,PBADate,PBPNDate,RPREDate, CPBADate,QPRDate,
[Risk Number],
BusinessGroup,
[EDW Hub Name],
[Accounting Behaviour Code],
CostCentre,
OrigOffName,
UwtgYearAcct,
RiskCode,
[Insured Else Reinsured],
UWName,
UwtgRef,
IncptDate,
[Incpt Month],
[Pre-Bind Peer Review Date],
[BGL Approval Date],
[Pre-Bind Approval Date],
[Pre-Bind Peer Review Note],
[Post Bind Peer Review Date],
[Retrospective Peer Review Exempt],
[CUO pre-bind approval Date],
[Quote Peer Review Date],
PromisedDate,
QuotedDate,
WrtnDate,
[Risk Policy Type] as RPT,
[Section Policy Type],
[Policy Type],
NewRenwl,
[Policy Duration],
SgndStatus,
DeclnNmbr,
[Conv NA WEPI],
[Conv LimitExposure],
ActLine,
RnwlPcntChnge,
[Expected ULR],
PremAdeqPcnt

resident Peer_Review ;

drop Table Peer_Review;

Re: Removing duplicate records

lfetensini — Wed, 24 Apr 2019 16:50:35 GMT

If you need field ConcatField unique, them you should do some aggregation.

What is your aggregation condition? MinString(ConcatField) ?

Load
MinString(ConcatField) as ConcatField,
Field1,
Field2,
Field3
...
Resident TABLE
Group By
Field1,
Field2,
Field3
...

topic Removing duplicate records in QlikView

Removing duplicate records

Re: Removing duplicate records