Issue with Peek function

[#1]

Hi,

I need to identify customers with the same name but a different number.

If there are such customers, one of them will be flagged.

I used peek, but it doesn’t work.

All are flagged as ‘No’, even though there are doubles.

Or, maybe there is a better way to do it?

[Temp Customers Names]:

LOAD
Distinct
[Customer Name],
[Customer Number],
Company

Resident [Backlog Orders]
Order by  [Customer Name],[Customer Number];


Left Join ([Temp Customers Names])

LOAD
[Customer Name],
if([Customer Name] = Peek('[Customer Name]',-1) and [Customer Number]<> Peek('[Customer Number]',-1),
'Yes' ,'No') as DoubelCustomerName

Resident [Temp Customers Names];

Thanks!

[Gysbert_Wassenaar · Accepted solution]

I think this should work:

Temp:

Load [Customer Name], count(distinct [Customer Number]) as DupeCount

resident [Backlog Orders] group by [Customer Name];

Dupes:

Load [Customer Name]

resident Temp where DupeCount >1;

drop Table Temp;

[#3]

Very elegant!

Thanks!

[#4]

Hi Gysbert,

I would appreciate your advise in the following:

After identifying the duplicates, I need to manipulate their customer’s name,

By adding to it some characters from another field .

Another words, “update” the relevant names in the pre loaded transactions table ([Backlog Orders].

I did the following steps:

Left Join ([Backlog Orders])
LOAD

TempCustName,
TempCustName  as Duplicate

Resident Duplicates;

[New Backlog]:
LOAD
if(Duplicate = TempCustName, TempCustName &' - '& Right(Company,4), TempCustName) as [Customer Name];

Another words, I did a new load to the whole table, because left join didn’t enable me to do it.

Is there a better way to achieve this?

Thanks!