Numbering the customer name

[felipe_oliveira]

Hi Masters,

If a customer belongs to a specific category, we do not want to disclose the customer name; thus I was wondering if it is possible to replace the customer name with "Customer 1", "Customer 2", "Customer 3" ...etc during the load.

Therefore, every time the category falls under "CMarket" we want to replace the customer name by "Customer" + "Autonumber" = Customer 1, Customer 2 etc

The numbering should be automatic for every new customer under CMarket category.

Currently:

Category	Customer Name
Vendor	John's Transport
CMarket	Barbershop Michigan
CMarket	NY cellphone
Vendor	Mary's Food Truck
CMarket	Texas Printer
Direct	Fast Car Rental

Expected:

Category	Customer Name
Vendor	John's Transport
CMarket	Customer 1
CMarket	Customer 2
Vendor	Mary's Food Truck
CMarket	Customer 3
Direct	Fast Car Rental

Thanks for your help

[Lisa_P · Accepted solution]

One way to do this is with a mapping table.

A mapping table is a bit like a Vlookup where you can replace values with what you find in the lookup.

So in this case, create a mapping table before you load the table with the Customers.

CMarket_Mapping:

mapping load

[Customer Name]  as OldName,

'Customer '& rowno() as NewName

from .....

where Category='CMarket';

Then in the Customers table as you load the customers, you replace them with the lookup using ApplyMap

ApplyMap('CMarket_Mapping', [Customer Name], [Customer Name]) as [Customer Name],

In place of [Customer Name] field.

[MindaugasBacius · Accepted solution]

Hei,

I would script like it like this:

temp:
LOAD Category,
[Customer Name],
AutoNumber(RowNo(), Category) as number
FROM
[https://community.qlik.com/t5/New-to-QlikView/Numbering-the-customer-name/td-p/1602760]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

NoConcatenate
tmp:
LOAD *
,If(Category = 'CMarket', Category & number, [Customer Name]) as new_customer_name
Resident temp;

DROP Table temp;

Results: