topic Re: Flagging Duplicates in QlikView

Flagging Duplicates

dmac1971 — Tue, 31 Mar 2015 12:21:54 GMT

I can easily enough find what records are duplicated :

if (peek('Line_ID',-1) = Line_ID ,'Dup','NoDup') as DupRec

But this will only mark the 2nd record, I need to mark the first one as well, ie mark them both as duplicate?

Re: Flagging Duplicates

sunny_talwar — Tue, 31 Mar 2015 12:38:23 GMT

So, if I am thinking this correctly, I think the only places that would be left would be the places where you have 1st duplicates cause everything else will either have Dup or NoDup. Can you do something like this:

YourTable:

LOAD If(Len(Trim(DupRec)) = 0, 'Dup', DupRec) as DupRec;

LOAD if (peek('Line_ID',-1) = Line_ID ,'Dup','NoDup') as DupRec

....

From/Resident xyz

Order by Line_ID;

Let me know if it doesn't work.

Best,

Sunny

Re: Flagging Duplicates

Tue, 31 Mar 2015 13:35:45 GMT

Hi Dermot,

Try like this....

if(Peek('Line_ID')=Line_ID,0,1) as Flag

from resident table

order by Line_ID

Re: Flagging Duplicates

dmac1971 — Tue, 31 Mar 2015 13:39:26 GMT

Still gives the same result, only in reverse.

Re: Flagging Duplicates

datanibbler — Tue, 31 Mar 2015 13:41:45 GMT

Hi Dermot,

I think, since QlikView loads the table one record at a time, from the top down, there's no way to do this in one go - you'll have to do one RESIDENT LOAD in reverse sorting order and do the same as the first time round.

Best regards,

DataNibbler

Re: Flagging Duplicates

sunny_talwar — Tue, 31 Mar 2015 13:41:56 GMT

Did you take a shot at what I suggested? Just curious

Re: Flagging Duplicates

Tue, 31 Mar 2015 13:51:59 GMT

Try Like this....

Order by Line_ID DEC

Re: Flagging Duplicates

Tue, 31 Mar 2015 13:54:34 GMT

Could you use Peek('Line-ID', RowNo()+1,'table') to check for the next to get a flag on the first one too.

Re: Flagging Duplicates

dmac1971 — Tue, 31 Mar 2015 14:07:59 GMT

OK Trying This :

If (Peek('Line_ID',-1) = Line_ID,'Dup', If(Peek('Line-ID', RowNo()+1,'Orders') = Line_ID,'Dup','NoDup')) as DupRec

Doesn't work either....

Re: Flagging Duplicates

sunny_talwar — Tue, 31 Mar 2015 14:28:01 GMT

I guess for some reason you hate my suggestion. I was just trying to help.

I hope you get what you are looking for.

Best,

Sunny

Re: Flagging Duplicates

dmac1971 — Tue, 31 Mar 2015 14:35:05 GMT

Apologies, not ignoring it, trying to go with the solution I understand better, that's all.

Re: Flagging Duplicates

marcus_malinow — Tue, 31 Mar 2015 14:37:16 GMT

Why don't you try something like:

LOAD

[your original load]

;

LEFT JOIN (Your Original Table)

LOAD

Line_ID,

RangeMin(Count(Line_ID), 1) as DuplicateFlag

RESIDENT

(Your Original Table)

GROUP BY Line_ID;

Re: Flagging Duplicates

sunny_talwar — Tue, 31 Mar 2015 14:43:54 GMT

Not a problem.

Best,

Sunny

Re: Flagging Duplicates

oknotsen — Tue, 31 Mar 2015 14:45:29 GMT

The solution is fooling around with a few Resident loads.

Lets assume the table with your original IF statement is called TableTMP.

DoubleTMP:

Mapping

Load Line_ID as DoubleID,

'Dub' as Flag

Resident TableTMP

where DupRec = 'Dub';

That makes a helper list with all the keys that are double.

Next, create your final table:

TableFinal:

NoConcatinate

Load

AllKindOfFieldsFromTableTMP,

applymap('DoubleTMP', Line_ID, 'NotDub') as DupRec

Resident TableTMP

;

Drop Table TableTMP;

I assume it can be done easier, but this should work.

Re: Flagging Duplicates

Tue, 31 Mar 2015 15:47:54 GMT

Or yet another solution... count.

countduplicates:

MAPPING LOAD DISTINCT Line_ID,

count(some field) As duplicates

RESIDENT table Group by Line_ID;

finaltable:

LOAD Line_ID,

Applymap('countduplicates',Line_ID,0)

RESIDENT table;

Re: Flagging Duplicates

malini_qlikview — Tue, 31 Mar 2015 15:58:42 GMT

Try This

Load
Line_id,
If(Count(Line_id)>1,'Dup','No_Dup') AS DupRec
Resident Table
Group by Line_id;

Re: Flagging Duplicates

sasiparupudi1 — Mon, 01 Jun 2015 12:43:18 GMT

Please try

LOAD *Inline [SalesID,SalesAmount

1,100

1,7

2,200

3,400

4,60

5,2000

6,900

];

LOAD SalesID,SalesAmount,

if ((peek('SalesID',-1)= SalesID or peek('SalesID',1)= SalesID),'Dup','NoDup') as DupRec

Resident x

order by SalesID;

drop Table x;