Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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:
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.
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: