Loop for duplicates search QlikView

[zyevniyeva]

Hello,

I have an interesting problem to solve.

So we have uploaded a database to QlikView, a database which has had such an issue that every time a user uploads new data entry, instead of rewriting the row with the same ID, it creates a new one with unique ID. The approach I have chosen is the loop inside the script that checks whether the two descriptions (in two columns) are equal, if yes - the script marks the ID as max, this way, every time the entry has matching descriptions, it chooses the one with the maximum ID. The loop seems to be okay but the problem is with the max attribution. Can anyone please have a look and comment on what might be changed? Would be glad for any help.

Bit of script is below.

LOAD R4Factor_ReportId, R4Factor_Id, R4Factor_General_EvalVolume, R4Factor_General_EvalMoney,

     R4Factor_General_FreqPerYear, R4Factor_General_EvalVolume_Ost, R4Factor_General_EvalMoney_Ost,

     R4Factor_General_FreqPerYear_Ost, R4Factor_DamageQuarterVolume, R4Factor_DamageYearVolume,

     R4Factor_DamageQuarterMoney, R4Factor_DamageYearMoney, R4Factor_WeightRisk,

     R4Factor_WeightArea, R4Factor_UpperLimitOfDamageQuarter, R4Factor_UpperLimitOfDamageYear,

     R4Factor_RiskFactorName, R4Factor_BusinessDirectionName

FROM QVD\Report4_RiskFactors.qvd (qvd);

Report4_RiskFactors:

SET i = 1;

LET n = NoOfRows('Report4_RiskFactors');

DO while $(i) <= $(n)

SET j = 1;

LET max = 0;

DO while $(j) <= $(n)

LOAD Peek('R4Factor_RiskFactorName',$(i),'Report4_RiskFactors') as RiskName1;

LOAD Peek('R4Factor_RiskFactorName',$(j),'Report4_RiskFactors') as RiskName2;

LOAD Peek('R4Factor_BusinessDirectionName',$(i),'Report4_RiskFactors') as RiskBusinessDir1;

LOAD Peek('R4Factor_BusinessDirectionName',$(j),'Report4_RiskFactors') as RiskBusinessDir2;

LOAD Peek('R4Factor_Id',$(i),'Report4_RiskFactors') as RiskID1;

LOAD Peek('R4Factor_Id',$(j),'Report4_RiskFactors') as RiskID2;

if ((RiskName1 = RiskName2) and (RiskBusinessDir1 = RiskBusinessDir2))THEN

if (RiskID1 > RiskID2) THEN LET max = $(RiskID1); //as r4Factor_Id

elseif (RiskID2 > $(max)) THEN ($(max) = RiskID2);

end if

end if

$(j) = $(j)+1;

LOOP

r4factor_Id_table:

LOAD $(max) as r4Factor_Id; 

$(i) = $(i)+1;

LOOP

[rwunderlich · Accepted solution]

I think your code may be more complex than necessary.  If you wanted to keep only the highest ID for duplicates and delete others:

Inner Join (Report4_RiskFactors)

LOAD max(R4Factor_Id) as R4Factor_Id

Resident Report4_RiskFactors

Group By R4Factor_RiskFactorName, R4Factor_BusinessDirectionName;

If you want to collect the max ids instead of deleting rows, remove the Inner Join.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com