Using a condition to filter duplicate lines from the dataset

[#1]

Good day to everyone,

I have a data-set that contains risk data, now I want to filter the data based on the following condition:

for all duplicates of id_acc, execute the following:

1. data set must is arranged (asc) on the account_number, on the same month (date), and
2. on the second line, if the account_number's first 3 digits start with '999' or '333' and lastly
3. customer_key on both lines are the same

Then remove both lines from the data set,

Below is the illustration of what I described above

I have tried to create a flag if the above conditions are satisfied, with the following code:

if((id_acc = previous(id_acc)) and match(mid(ACCOUNT_NUMBER,1,3),'333','999') and (CUSTOMER_KEY = Previous(CUSTOMER_KEY)),1,0) as [delete_flag]

And its not working when I do the count and sum on the front end.

Please assist.

I thank you in advance

[#2]

Hi Rohit,

How do I share data on this site?

[#3]

Hi Colin,

How do I then do the count and the sum on the front-end and disregard those lines?

[PradeepReddy]

see the attachment, are you looking something like this..

[Colin-Albert]

=sum({<delete_flag={0}>}  FieldNameToCount)

The set expression will only select rows that have the delete_flag set to 0

Replace FieldNameToCount with the correct field name

[#6]

Colin,

On the two line that have the same id_ac, what if I want to delete the one above?

[Colin-Albert · Accepted solution]

You cannot delete a row directly in QlikView.

The only option you have is to load the data into another table using a resident load, with NoConcatenate and a where clause to exclude the rows you wish to remove, and then drop the original table.

[#8]

Thanks a lot