I am working with a big shipment database in Qlik sense. This data comes from the data warehouse. In this database, certain customer names are not filled in correctly. So i have made an excel file with two columns: "wrong customer name" and "new customer name". Now I want to add a formula in the data manager with the following conditions:
- If the customer name from the data warehouse exists in the column wrong customer name, the field should show the new customer name
- If the customer name from the data warehouse does not exist in the column wrong customer name, the field should show the customer name from the data warehouse.
This way i will have one new column with all the right customer names from the data warehouse combined with the new customer names from the Excel file.
I tried to start with this, but i noticed that you cannot access column names of another source in a calculated field in the data manager.
Has anyone a solution for this?
Thanks in advance
Not sure but you may modify your script to add required fields, I do everything through manual script so not exactly sure if it can be done after the auto generated code from data manager.
You probably can do what you are after with a mapping table but as the other posters have indicated you won't be able to do that with the data manager. You will have to use the data load editor.
Okay I have tried to modify the script in the load editor. I have added the script shown below, but i get the following error: Field 'new_customer_name' not found
LIB CONNECT TO 'RTDW';
"CUSTOMER_NAME" AS CUSTOMER_LINKED,
if ("new_customer_name" = 0, CUSTOMER_LINKED, "new_customer_name") AS CUSTOMERS,
SQL SELECT ID,
FROM DWHADMIN."RTDW_SHIPMENTS" WHERE LAST_RECORD = 'Y';
"old_customer_name" AS CUSTOMER_LINKED,
FROM [lib://Data_Qlik/Customer names combined.xlsx]
(ooxml, embedded labels, table is Sheet2);
Here, I see two tables.
2. Customer names combined.xlsx
do you want to link these two table by the field Customer_name ?
If yes, why ? to be able to see the new_customer_name informations ?