Track Only Duplicate References

[rishikeshtiwari]

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

[HirisH_V7]

Hi,

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

[HirisH_V7]

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

[rishikeshtiwari]

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

[rishikeshtiwari]

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

[jansen28]

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