Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rishikeshtiwari
Creator
Creator

Track Only Duplicate References

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

5 Replies
HirisH_V7
Master
Master

Hi,

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

HirisH
“Aspire to Inspire before we Expire!”
HirisH_V7
Master
Master

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:

Purge Char Data-204551.PNG

Hope this Helps,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
rishikeshtiwari
Creator
Creator
Author

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

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

jansen28
Contributor III
Contributor III

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