Delete duplicate records

[jamessmith99]

MerchantData1

MerchantID	MerchantAmount
Z000005296	100
Z000004521	50
Z000001539	300

MerchantData2

MerchantID	MerchantAmount
Z000005296	25
Z000005296	25
Z000005296	50
Z000004521	25
Z000004521	25
Z000004521 (delete record) *	25 (delete record) *
Z000001539	100
Z000001539	100
Z000001539	100
Z000001539 (delete record) *	100 (delete record) *

I have two tables MerchantData1 and MerchantData2.  Both contain a MerchantID field with matching MerchantIDs but the MerchantIDs in MerchantData2 table are duplicated.  MerchantData1 and MerchantData2 tables also contain a MerchantAmount field.

I want to compare the sum of MerchantAmount field from MerchantData2 to the matching MerchantAmount from MerchantData1.  If the sum from the MerchantData2 MerchantAmount field table is greater than matching MerchantData1 MerchantAmount field I want to delete MerchantData2 records, from the bottom up, until MerchantData2 MerchantAmount sum matches MerchantData1 MerchantAmount.

Can you help?

[passionate · Accepted solution]

Hi James,

PFA , Solution.

Test:

LOAD MerchantID,

     MerchantAmount as AggrMerchantAmount

FROM

[https://community.qlik.com/thread/305865]

(html, codepage is 1252, embedded labels, table is @1);

left Join(Test)

LOAD MerchantID,

     MerchantAmount

FROM

[https://community.qlik.com/thread/305865]

(html, codepage is 1252, embedded labels, table is @2);

NoConcatenate

A:

LOAD *,if(Rolling<=AggrMerchantAmount,1,0) as limit;

LOAD *,if(MerchantID=Previous(MerchantID),Rangesum(MerchantAmount,peek('Rolling')),MerchantAmount) as Rolling;

LOAD * Resident Test

Order by MerchantID

;

DROP Table Test;