Compare current and previous record

[#1]

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

[othniel2014]

Store Name is the primary key?

See this information

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

[#3]

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

[vishsaggi]

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'

'S1','Grossary'

'S2','Summary1'

'S1','Gross'

'S2','Summary'

'S2','Summary'

];

Populate these values in a straight table with

Dim: StoreName, StoreDesc, RowCount

Expr: DuplicateCnt

In presentation tab hide Column RowCount.

[effinty2112]

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

[#6]

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

[vishsaggi]

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 ?

[#8]

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