topic Re: Combine different sources in the data manager in App Development

Combine different sources in the data manager

pascaldijkshoor — Fri, 16 Mar 2018 14:13:38 GMT

Hello,

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

Re: Combine different sources in the data manager

Digvijay_Singh — Fri, 16 Mar 2018 14:33:49 GMT

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.

Re: Combine different sources in the data manager

YoussefBelloum — Fri, 16 Mar 2018 14:50:09 GMT

Me too I not recommend using the data manager for this..it uses a lot of system resources (lots of graphics in it)

Re: Combine different sources in the data manager

Anonymous — Fri, 16 Mar 2018 16:04:54 GMT

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.

Re: Combine different sources in the data manager

pascaldijkshoor — Mon, 19 Mar 2018 11:18:05 GMT

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]

(ooxml, embedded labels, table is Sheet2);

Re: Combine different sources in the data manager

YoussefBelloum — Mon, 19 Mar 2018 12:59:28 GMT

It is maybe because you don't have new_customer_name field on your "RTDW_SHIPMENTS" table

Re: Combine different sources in the data manager

pascaldijkshoor — Tue, 20 Mar 2018 10:31:32 GMT

Yes that's the reason for the error. But is there a way to solve this?

Re: Combine different sources in the data manager

YoussefBelloum — Tue, 20 Mar 2018 10:34:39 GMT

Here, I see two tables.

1. "RTDW_SHIPMENTS"

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 ?