topic Re: Duplicate Scenarios in QlikView

Duplicate Scenarios

Anonymous — Tue, 14 Jun 2016 11:45:16 GMT

Hi,

I am interested in showing multiple duplicate scenarios in the file.

E.g

Duplicate of combination of same invoice num + same vendor num + same amount

Combo of same invoice num + invoice date

Combo of same invoice num + vendor num

So, for the first one I had concatenated the above mentioned fields and called it as the following

[Invoice Number] & [Vendor Number] & [Payment Amount] as C_Duplicate,

now to show the duplicate records I created a listbox with the formula

=if(Count(C_Duplicate) > 1, 'T', 'F') but I am not getting the desired result.

Basically I want a table/chart showing all the potential duplicate records from all the scenarios.

Attached the sample format of how I the result is expected

Re: Duplicate Scenarios

m_woolf — Tue, 14 Jun 2016 12:35:53 GMT

Can you share your qvw?

Re: Duplicate Scenarios

Digvijay_Singh — Tue, 14 Jun 2016 12:53:00 GMT

I think it works, see attached sample -

Load *, [Invoice Num] & [Vendor Num] & [Invoice Amount] as C_Duplicate;

LOAD [Invoice Num],

[Vendor Num],

[Invoice Date],

[Invoice Amount]

FROM

[Duplicate Scenarios.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Duplicate Scenarios

Anonymous — Wed, 15 Jun 2016 03:09:45 GMT

Hi Digvijay,

Thanks for the help, it works fine but the this is just a small sample so the user can click on T, what if there are a lot of line items with T, the 'T' option isn't showing in the search option.

Any idea where we can just show only the "True" exceptions? Maybe enable conditional enabling or something.

Re: Duplicate Scenarios

Anonymous — Wed, 15 Jun 2016 03:11:35 GMT

Attaching qvw

Re: Duplicate Scenarios

Anonymous — Wed, 15 Jun 2016 03:12:59 GMT

Possible to show all 3 exceptions in one place with the type of exceptions identified?

So only show records which are true in all 3 cases of duplicates and add a column saying which category it belongs to

Re: Duplicate Scenarios

Digvijay_Singh — Wed, 15 Jun 2016 04:54:39 GMT

See if this can help -

Re: Duplicate Scenarios

Anonymous — Wed, 15 Jun 2016 06:00:44 GMT

Hi Digvijay,

It's close but I would want to see both the duplicate records as well as the other fields next to it. In this case, it's showing only 1 duplicate case, so for the first duplicate case I should see two lines not just one

Re: Duplicate Scenarios

Digvijay_Singh — Wed, 15 Jun 2016 06:43:09 GMT

Actually it was showing both records but hiding as I was using chr(13) while concatenating, now I changed concat char to ',', see the updated one below - I am still trying how to show rest of the fields.

Re: Duplicate Scenarios

Digvijay_Singh — Wed, 15 Jun 2016 07:44:11 GMT

Below is better and more effective as it is script based solution, but when all the fields are same, for one of the case, I had to concatenate amount as QV is not showing complete duplicate records in straight table.

Please close the thread in case it satisfies the needs.

Re: Duplicate Scenarios

effinty2112 — Wed, 15 Jun 2016 07:44:48 GMT

Hi Digvijay,

This script

DataWithAutoID:

Load

AutoNumber([Invoice Num] & '|' & [Vendor Num] & '|' & [Invoice Amount],'AutoID1') as AutoID1,

AutoNumber([Invoice Num] & '|' & [Vendor Num] & '|' & [Invoice Amount],'AutoID2') as AutoID2,

AutoNumber([Invoice Num] & '|' & [Vendor Num] & '|' & [Invoice Amount],'AutoID3') as AutoID3

Resident Sheet1;

Drop table Sheet1;

gives a unique number ID for each of the combos you're looking for duplicates in.

Below is a straight table with no expressions with the option "suppress zero values unchecked". the first three columns are calculated dimensions. The first is:

=If(Aggr(Count(AutoID1), AutoID1)>1,'Yes')

Dup AutoID1	Dup AutoID2	Dup AutoID3	Invoice Amount	Invoice Date	Invoice Num	Serial_No	Vendor Num
			1000	3/3/2015	111	3	987
			2000	3/3/2015	111	4	7665
Yes	Yes	Yes	2000	5/20/2016	123	1	1234
Yes	Yes	Yes	2000	5/20/2016	123	2	1234
			8000	10/11/2013	222	6	566
			9000	9/9/2014	222	5	566

All the best

Cheers

Andrew

Re: Duplicate Scenarios

Digvijay_Singh — Wed, 15 Jun 2016 08:19:30 GMT

Right, this one has improved scripting

Now Sunny can take best of all.

Re: Duplicate Scenarios

Anonymous — Thu, 16 Jun 2016 09:29:11 GMT

Thanks all, appreciate all your help

Re: Duplicate Scenarios

Anonymous — Thu, 16 Jun 2016 09:29:36 GMT

Thanks Andrew, appreciate it