topic Re: Compare current and previous record in QlikView

Compare current and previous record

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to compare column value between current and previous record to identify duplicates , how to do so?

example

Store Name, Store Description

Previous Record: 'S1',' Grossary'

Current Record: 'S1', 'Grossary'

In above example I want to compare 'Store Name' column values between Previous and Current records

Re: Compare current and previous record

othniel2014 — Thu, 25 Aug 2016 21:25:36 GMT

Store Name is the primary key?

See this information

https://community.qlikview.com/thread/159455

Re: Compare current and previous record

Thu, 25 Aug 2016 21:44:09 GMT

no its not but this is just criteria to identify "Duplicate" as per business it has nothing to do with actual meaning of duplicates

Re: Compare current and previous record

vishsaggi — Thu, 25 Aug 2016 22:06:04 GMT

May be try this : I am not sure if this is what you are looking for. Added some dummy data below:

LOAD *,

RowNo() AS RowCount,

IF(StoreName = Previous(StoreName), 'Duplicate', 'Not Duplicate') AS DuplicateCnt;

Load * Inline [

StoreName, StoreDesc

'S1','Grossary'

'S2','Summary1'

'S1','Gross'

'S2','Summary'

];

Populate these values in a straight table with

Dim: StoreName, StoreDesc, RowCount

Expr: DuplicateCnt

In presentation tab hide Column RowCount.

Re: Compare current and previous record

effinty2112 — Thu, 25 Aug 2016 22:41:33 GMT

Hi Vibhu,

In your load script add this line:

Autonumber(StoreName & '|' & StoreDesc, 'CheckID') as CheckID

Then

Left Join

Load

CheckID,

Count(CheckID) as CountID

Resident YourTable Group by CheckID;

Now if you select in your dashboard CountID >1 you will see the duplicates.

Regards

Andrew

Re: Compare current and previous record

Fri, 26 Aug 2016 02:45:46 GMT

I am unable to sort the records in Load script due to this reason Previous function is not getting applied in correct way it should be

Re: Compare current and previous record

vishsaggi — Fri, 26 Aug 2016 03:07:59 GMT

Oh ok. Then probably it is not the comparison with current to previous, it should be any occurrence of StoreName more than once should be considered as duplicate? Did you try Andrew's script ?

Re: Compare current and previous record

Fri, 26 Aug 2016 03:35:35 GMT

report should not be filtered on duplicates it should show both duplicates as well as non duplicates