topic Numbering the customer name in QlikView

Numbering the customer name

felipe_oliveira — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Numbering the customer name

Lisa_P — Wed, 17 Jul 2019 00:17:14 GMT

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.

Re: Numbering the customer name

MindaugasBacius — Wed, 17 Jul 2019 05:42:26 GMT

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: