topic Using a condition to filter duplicate lines from the dataset in QlikView

Using a condition to filter duplicate lines from the dataset

Mon, 22 Feb 2016 09:34:24 GMT

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:

data set must is arranged (asc) on the account_number, on the same month (date), and
on the second line, if the account_number's first 3 digits start with '999' or '333' and lastly
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

Re: Using a condition to filter duplicate lines from the dataset

sunny_talwar — Mon, 22 Feb 2016 10:39:17 GMT

Are you getting the delete flag on the correct places after your load the data?

Re: Using a condition to filter duplicate lines from the dataset

Mon, 22 Feb 2016 10:49:44 GMT

Hi Sunny,

This is the code on the script:

LOAD

Date(MakeDate(Left(info_Date,4),mid(info_Date,5,2),01), 'YYYY/MM/DD') as [Date],
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]

From $(vPortfolioQVDs)\Portfolio_201512.qvd (qvd)

Re: Using a condition to filter duplicate lines from the dataset

rohit214 — Mon, 22 Feb 2016 11:12:24 GMT

hi can you share some test data?

Re: Using a condition to filter duplicate lines from the dataset

sathishkumar_go — Mon, 22 Feb 2016 11:21:49 GMT

please share the sample data

-Sathish

Re: Using a condition to filter duplicate lines from the dataset

sunny_talwar — Mon, 22 Feb 2016 11:27:53 GMT

I asked an Incorrect question and was corrected by Colin Below

Do you have a order by statement after this? From $(vPortfolioQVDs)\Portfolio_201512.qvd (qvd)?

Re: Using a condition to filter duplicate lines from the dataset

Colin-Albert — Mon, 22 Feb 2016 11:33:47 GMT

You cannot specify a sort order on a QVD load directly.

You will need to load the QVD into a temp table first , then do a resident load from the temp table specifying the required sort order.

Re: Using a condition to filter duplicate lines from the dataset

sunny_talwar — Mon, 22 Feb 2016 11:36:40 GMT

That's true

Re: Using a condition to filter duplicate lines from the dataset

Colin-Albert — Mon, 22 Feb 2016 11:40:16 GMT

Try this

tmpPortfolio:

load

Date(MakeDate(Left(info_Date,4),mid(info_Date,5,2),01), 'YYYY/MM/DD') as [Date],
*

From $(vPortfolioQVDs)\Portfolio_201512.qvd (qvd)

;

Portfolio:

noconcatenate

LOAD

* ,

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]
resident tmpPortfolio

order by id_acc , Date

;

drop table tmpPortfolio ;

Re: Using a condition to filter duplicate lines from the dataset

PradeepReddy — Mon, 22 Feb 2016 12:22:21 GMT

issue is due to sorting of the data in Account_Number column..

Edit: Screen shot added for the explanation...

Re: Using a condition to filter duplicate lines from the dataset

Mon, 22 Feb 2016 13:15:42 GMT

Hi Rohit,

How do I share data on this site?

Re: Using a condition to filter duplicate lines from the dataset

Mon, 22 Feb 2016 13:31:29 GMT

Hi Colin,

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

Re: Using a condition to filter duplicate lines from the dataset

PradeepReddy — Mon, 22 Feb 2016 14:10:04 GMT

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

Re: Using a condition to filter duplicate lines from the dataset

Colin-Albert — Mon, 22 Feb 2016 14:44:43 GMT

=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

Re: Using a condition to filter duplicate lines from the dataset

Tue, 23 Feb 2016 07:21:20 GMT

Colin,

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

Re: Using a condition to filter duplicate lines from the dataset

Colin-Albert — Tue, 23 Feb 2016 09:31:53 GMT

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.

Re: Using a condition to filter duplicate lines from the dataset

Tue, 23 Feb 2016 10:33:31 GMT

Thanks a lot