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.
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';
LOAD ID,
"SHIPMENT_NUMBER",
"CUSTOMER_CODE",
"CUSTOMER_NAME" AS CUSTOMER_LINKED,
if ("new_customer_name" = 0, CUSTOMER_LINKED, "new_customer_name") AS CUSTOMERS,
"LOAD_INDEX",
"L_LOCATION_CODE",
"L_BEGIN_DATE",
"LAST_RECORD";
SQL SELECT ID,
"SHIPMENT_NUMBER",
"CUSTOMER_CODE",
"CUSTOMER_NAME",
"LOAD_INDEX",
"L_LOCATION_CODE",
"L_BEGIN_DATE",
"LAST_RECORD"
FROM DWHADMIN."RTDW_SHIPMENTS" WHERE LAST_RECORD = 'Y';
LOAD
"old_customer_name" AS CUSTOMER_LINKED,
"new_customer_name"
FROM [lib://Data_Qlik/Customer names combined.xlsx]
