Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
sagar_vij99
Creator
Creator

Removing duplicate records

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;

1 Reply
lfetensini
Partner
Partner

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
...
Support your colleagues. Remember to "like" the answers that are helpful to you and flag as "solved" the one that helped you solve. Cheers.