Replacing a value in multiple columns

Great_to_learn — Thu, 08 Jul 2021 22:50:15 GMT

I have two different excel documents, one is a template document with 5 columns showing the desired qvw output format, while the other document has one field which is the customer name. I am trying to replace the default value(AAA) with the Customer name so I don't have to change this manually from the excel sheet for every customer added. Any ideas on how to go about this?

Below is an example of the two Excel files I have:

Customer Name	Number	File	Prod Name	User Groups
AAA		AAA_Europe	AAA_Europe_Report	EuropeGroup_AAA
AAA		AAA_America	AAA_America_Report	AmericaGroup_AAA
AAA		AAA_Asia	AAA_Asia_Report	AsiaGroup_AAA

Customer Name

Customer 1

Customer 2

Customer 3

Expected Result:

Customer Name	Number	File	Prod Name	User Groups
Customer 1		Customer 1_Europe	Customer 1_Europe_Report	EuropeGroup_Customer 1
Customer 1		Customer 1_America	Customer 1_America_Report	AmericaGroup_Customer 1
Customer 1		Customer 1_Asia	Customer 1_Asia_Report	AsiaGroup_Customer 1
Customer 2		Customer 2_Europe	Customer 2_Europe_Report	EuropeGroup_Customer 2
Customer 2		Customer 2_America	Customer 2_America_Report	AmericaGroup_Customer 2
Customer 2		Customer 2_Asia	Customer 2_Asia_Report	AsiaGroup_Customer 2
Customer 3		Customer 3_Europe	Customer 3_Europe_Report	EuropeGroup_Customer 3
Customer 3		Customer 2_America	Customer 3_America_Report	AmericaGroup_Customer 3
Customer 3		Customer 3_Asia	Customer 3_Asia_Report	AsiaGroup_Customer 3

Re: Replacing a value in multiple columns

MayilVahanan — Fri, 09 Jul 2021 04:11:33 GMT

Hi @Great_to_learn

Try like below

TEMP:
LOAD * INLINE [
Customer Name, Number, File, Prod Name, User Groups
AAA, , AAA_Europe, AAA_Europe_Report, EuropeGroup_AAA
AAA, , AAA_America, AAA_America_Report, AmericaGroup_AAA
AAA, , AAA_Asia, AAA_Asia_Report, AsiaGroup_AAA
];

Join
LOAD [Customer Name] AS Customer Inline
[
Customer Name
Customer 1
Customer 2
Customer 3
];

Load
Replace([Customer Name], 'AAA', Customer) as [Customer Name], Number,
Replace(File, 'AAA', Customer) as File, Replace([Prod Name], 'AAA', Customer) as [Prod Name],
Replace([User Groups], 'AAA', Customer) as [User Groups]
Resident TEMP;

DROP Table TEMP;

topic Re: Replacing a value in multiple columns in QlikView

Replacing a value in multiple columns

Re: Replacing a value in multiple columns