topic How to remove duplicate records while loading a table based on a column in QlikView

How to remove duplicate records while loading a table based on a column

pranaview — Sat, 16 Nov 2024 21:45:10 GMT

Hi Guys,

Problem : I need to load a table from an excel but need to remove duplicate records based on a column for same Order_Numbers. For ex:

Below image shows the sample records in the Orders sheet that has unique Order_Number

Below image is of the MappingSheet in which I want to remove the duplicate records for each Order_Number based on the column "IssueType". So, MappingSheet can have multiple records for one Order_Number but what iIwant is to load only those Order_Numbers which have different IssueType.

So, only highlighted records should be loaded into Qlik. Note that IssueType can be same for different orders as is the case for Order_Number 1 and 2. I have to check for the duplicate IssueType for each Order_Number and if there are more than one records with same IssueType for a single Order_Number then I have to pick only one with the oldest date.

Hope, I have made the requirement clear enough but if not please let me know. I need to come up with a solution real quick for this one so any suggestion would be of great help.

Thanks,

Pranav

Re: How to remove duplicate records while loading a table based on a column

FelipeFuku — Mon, 10 Dec 2018 12:05:16 GMT

Hey Pranaview,

Ok, so first I think you need to make a stronger %Key, like:

Order_Number &' | '& IssueType as %Key

After doing that, you can try using count... Like...

If( count(%Key)>1 , max (Date_Auto) , Date_Auto)

I think it should work.. maybe not the best solution, but it's the first thing that came into my mind ...

Note that i didn't test it, so let me know if it works..

Hope that I've helped you.

Regards, Fuku.

Re: How to remove duplicate records while loading a table based on a column

pranaview — Mon, 10 Dec 2018 12:29:35 GMT

Hi Fuku,
Thanks a lot for the suggestion.
I have a question. Where should i add the If condition? During the load? or in the charts? cos what i want is to just not load the duplicate records into Qlik.

Re: How to remove duplicate records while loading a table based on a column

madhudinesh — Mon, 10 Dec 2018 12:29:38 GMT

Try with the below

T:
LOAD [Order Number],
Team,
[Issue Type],
Comment,
Date
FROM
[z:\xxx.xlsx]
(ooxml, embedded labels, table is Sheet2);

T1:
Load *
Where Flag='Yes';

Load *,
if([Order Number]&[Issue Type]=Previous([Order Number]&[Issue Type]),'No','Yes') as Flag
Resident T order by [Order Number] asc,[Issue Type] asc;

DROP Table T;

Re: How to remove duplicate records while loading a table based on a column

FelipeFuku — Mon, 10 Dec 2018 12:32:14 GMT

Well, if you don't want to load the duplicate, you can add it to the script, but you'll need to "Group by" the other fields...

Re: How to remove duplicate records while loading a table based on a column

kjhertz — Mon, 10 Dec 2018 12:34:06 GMT

There are many ways to achieve this. You can construct a composit key and use firstsortedvalue on all the fields etc perhaps using an inner Join by RowNo would be faster. Be aware that you are using a transparent rule regarding which rows you keep and discard, in the example below we take the first value sorted by date.

source:
load
*,
RowNo() as RowNo
;
load * inline [
OrderNo, IssueType, DateId
1,1,1
1,1,2
1,2,2
2,1,3
2,5,4
3,6,5
4,7,6
4,7,8
5,9,8
5,10,9
5,10,10
];

inner join (source)

Load
FirstSortedValue(RowNo,DateId) as RowNo
Resident source group by OrderNo, IssueType;

Re: How to remove duplicate records while loading a table based on a column

FelipeFuku — Mon, 10 Dec 2018 12:43:28 GMT

You can do something like this:

Load *,

Max(Date_Auto) as Date_Auto,

count( DISTINCT Order_Number &' | '& IssueType) as %Key,

From [] Group by ;

You need to put all other fields in Group , except by Key fields and Date;

Hope it helps Pranav.

Fuku.

Re: How to remove duplicate records while loading a table based on a column

pranaview — Mon, 10 Dec 2018 13:32:15 GMT

Hi Fuku,
So, I tried doing what has suggested in your first post. I created a composite key of Order_Number and IssueType i.e. %Key while loading the data.
Then I tried to use firstsortedvalue() function to get unique records based on the composite key as below
Load %Key,
Date(Max(Date_Auto),'MM/DD/YYYY') as Date_Auto,
FirstSortedValue(Distinct Order_Number,-Date_Auto) as Order_Number,
FirstSortedValue(Distinct Team,-Date_Auto) as Team,
FirstSortedValue(Distinct IssueType,-Date_Auto) as IssueType,
FirstSortedValue(Distinct Comment,-Date_Auto) as Comment
Resident Delivery Group by %Key;

But in the Table view, i am just getting value in %Key column, rest are all null.

Pranav

Re: How to remove duplicate records while loading a table based on a column

pranaview — Mon, 10 Dec 2018 13:41:39 GMT

Hi Jonas,
Thanks for the response.
I tried the first approach i.e. creating a composite key of Order Number and IssueType and then using firstsortedvalue() by Date_Auto field and Group By on the composite key but in the result I am not getting any values except in the composite key column.

I tried the script that you have pasted here but it is not showing any data in the Table View.
I am really new to QV. Hence the struggle.

Thanks,
Pranav

Re: How to remove duplicate records while loading a table based on a column

kjhertz — Mon, 10 Dec 2018 14:44:05 GMT

Can you post the script that you have tried here?

Re: How to remove duplicate records while loading a table based on a column

FelipeFuku — Mon, 10 Dec 2018 16:26:35 GMT

Hmm, try the last suggestion that i've made...

Without a composite the first Key, only :
max(Date_Auto) as Date_Auto,
count( DISTINCT Order_Number &' | '& IssueType) as %Key

And group by all other fields.
I think it should work... You don't even need to use FirstSortedValue() in this case..

Regards, Fuku.

Re: How to remove duplicate records while loading a table based on a column

FelipeFuku — Wed, 12 Dec 2018 10:24:20 GMT

Hello again, Pranav.

Did you find a solution? Or did our suggestions worked?

Let us know, please!

Best Regards, Fuku.

Re: How to remove duplicate records while loading a table based on a column

pranaview — Wed, 12 Dec 2018 14:59:09 GMT

Hi Fuku,

I was not able to make it work with your latest suggestion. Although I made it work with a slight tweak in the solution provided by @madhudinesh. But Yesterday, the client again changed(surprise surprise) their mind and now they want the duplicate values to be displayed as well so that they can catch the issue in the source and directly fix it there.But anyway thanks a lot to you and everyone else for pitching in with valuable suggestions. I will close this thread now.

Pranav

Re: How to remove duplicate records while loading a table based on a column

bgerchikov — Fri, 04 Jan 2019 21:49:41 GMT

Try this:

TempData:
Load * Inline
[
Order_Number,Team,IssueType,Comment,Date_Auto
1,Team1,Issue1,Oldest Logged,43400
1,Team2,Issue1,Comment2,43401
1,Team3,Issue2,Comment3,43401
2,Team4,Issue1,Comment4,43402
2,Team5,Issue1,Comment5,43403
2,Team6,Issue4,Comment6,43404
]
;
MaxDate:
NoConcatenate Load
   Order_Number,
    IssueType,
   Min(Date_Auto) as Date_Auto
Resident
   TempData
Group By
   Order_Number,
    IssueType
;
Left Join(MaxDate)
Load * Resident TempData;

Drop Table TempData;