topic Re: Track Only Duplicate References in QlikView

Track Only Duplicate References

rishikeshtiwari — Sat, 06 Feb 2016 16:43:40 GMT

Hi Guys,

I have a scenerio in a Vendor table.We have some fileds in a vendor table having two fields

Reference Actual and Reference Duplicate.In Actaul Reference there is Alfa-Numeric values and

in Reference duplicate field there is some special chars has benn used between these vales.

Actually some vendors have a filled a fake reference numbers using special characters in

existing Real references (if this reference already occured).So Real references are the acual

refrence and Reference Duplicate is fake reference if it already occurs once (before).

So If any row has already present in table (ignoring Duplicate References field) and it again

appearing due to Duplicate References which are different(new) then we need to capture only

those rows in table with existing rows.

We need to capture both References Real & Duplicate References.

Means we want those recors which are duplicate.

I have mentioned the result also.

Thanks
Rishi

Re: Track Only Duplicate References

HirisH_V7 — Sat, 06 Feb 2016 18:55:21 GMT

Hi,

is there any exact Match between two fields,Reference Actual and Reference Duplicate.

Re: Track Only Duplicate References

HirisH_V7 — Sat, 06 Feb 2016 19:11:14 GMT

Hi,

Check this,

using PurgeChar Function,

Data:
LOAD  Distinct
     Vendor, 
     [Document Date], 
     [Document Number], 
     Purgechar(Reference_Duplicate,'.,-,/,>,=,…., ,') as Reference_Duplicate, 
     [Reference Real]
FROM
[Vendor with Duplicate Ref.xlsx]
(ooxml, embedded labels, table is [Vendor with Duplicate Ref]);

Output:

Hope this Helps,

PFA,

Hirish

Re: Track Only Duplicate References

rishikeshtiwari — Sun, 07 Feb 2016 03:15:17 GMT

Hi Hirish,

Thank you.

My requirement is to capture those rows with Reference Real & Duplicate Reference where Reference is

repeated.Means I need only those rows in table where for a column Reference real is repeating due to Reference duplicate. I have to ignore rest of the columns that are not required. I need not require those rows where

Reference Real appears first time.

I have to capture only those rows where Reference Real is duplicating with previous value(duplicate).

I have attached the result also in Excel.

Thank

Rishi

Re: Track Only Duplicate References

rishikeshtiwari — Sun, 07 Feb 2016 05:46:44 GMT

Yes there may be any exact match. But we need to track only when Reference real occurs two times for a row.

Re: Track Only Duplicate References

jansen28 — Sun, 07 Feb 2016 08:59:40 GMT

Hi Rishi,

You can try this,

Step 1:

Firstly flag duplicates by comparing row-wise record using peek function,

if(Reference_Duplicate<>Peek(Reference_Duplicate,-1),1,0) as Capture_Duplicate

Step 2:

Load only Capture_duplicate Flag with 1

Regards,

Jan