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

[sunny_talwar]

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

[#3]

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)

[rohit214]

hi can you share some test data?

[sathishkumar_go]

please share the sample data

-Sathish

[sunny_talwar]

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)?

[Colin-Albert]

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.

[sunny_talwar]

That's true

[Colin-Albert]

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 ;

[PradeepReddy]

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

Edit:  Screen shot added for the explanation...