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