Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using a condition to filter duplicate lines from the dataset

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

45.jpg

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

1 Solution

Accepted Solutions
Colin-Albert

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.

View solution in original post

16 Replies
sunny_talwar

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

Not applicable
Author

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
Creator III
Creator III

hi can you share some test data?

sathishkumar_go
Partner - Specialist
Partner - Specialist

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
Specialist II
Specialist II

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


Edit:  Screen shot added for the explanation...

Test.png