Joining tables in Qlik Sense

[Tribhuwan]

Hi,

I am new to Qlik Sense and need the help of experts on urgent basis for my project.

Currently I am able to  join  2 tables in first step i.e. CSP and Table. So in next step I am joining the final table CSP to a new table from SAP HANA i.e. Segment.

Segment table has below 3 common columns along with others

GLOBAL_CUSTOMER_NUMBER
GLOBAL_CUSTOMER_NAME
GCN_ID_TYPE

I want these to be mapped in CSP table with the condition that wherever there is value in  FUP ID and GLOBAL_CUSTOMER_NUMBER columns and it is not matching with each other than it will update/replace the value of  GLOBAL_CUSTOMER_NUMBER, GLOBAL_CUSTOMER_NAME with what is there in FUP_ID and FUP_NAME columns.

I tried using if condition along with and operator but it is not working.

Below is my script:

CSP:
LOAD
S.no.,
"Account Name",
"DiscoverOrg ID",
IVCOMPANYID,
GLOBAL_CUSTOMER_NUMBER,
GLOBAL_CUSTOMER_NAME,
GCN_ID_TYPE
FROM [lib://Excel File Location (internal_tsingh1)/Qlik Test Data.xlsx]
(ooxml, embedded labels, table is Sheet2);

Table:
LIB CONNECT TO 'HANAPROD (internal_svc-qlikservice)';
Left Join(CSP)
LOAD "IVCITY",
"IVNAME",
"IVCOMPANYID" ,
"FUP_ID",
"FUP_NAME";
SQL SELECT "IVCITY",
"IVCOMPANYID",
"IVNAME",
"FUP_ID",
"FUP_NAME"
FROM "EDWC_CAMPAIGNS"."CVF_T_MKT_IV_COMPANYDETAILS";

Segment:
Left Join(CSP)
LOAD "FUP_ID",
"FUP_NAME"
if(GLOBAL_CUSTOMER_NUMBER<>"FUP_ID" and "FUP_ID"<>Null(),FUP_ID,GLOBAL_CUSTOMER_NUMBER)as "GLOBAL_CUSTOMER_NUMBER",
"GLOBAL_CUSTOMER_NAME",
"GCN_ID_TYPE";
SQL SELECT 
"FUP_ID",
"FUP_NAME",
"GLOBAL_CUSTOMER_NUMBER",
"GLOBAL_CUSTOMER_NAME",
"GCN_ID_TYPE"
FROM "EDWC_CAMPAIGNS"."CVF_T_MKT_SEGMENTATION_ANALYSIS";

Below is the screenshot which explains what I am trying to say             

In the above image you can see that data in columns FUP_ID and FUP_NAME (highlighted in yellow color) are different with what is there in columns GLOBAL_CUSTOMER_NUMBER and GLOBAL_CUSTOMER_NAME (highlighted in green color).

So I want that in final table wherever we have mismatch and wherever FUP_ID is available, it will update data in columns GLOBAL_CUSTOMER_NUMBER and GLOBAL_CUSTOMER_NAME

I hope I have not confused anyone.

Could anyone please advise me that how can it be done.

[Lauri · Accepted solution]

Qlik is a little funny, and not like some other programs. In order to compare values in two fields, you need to first load them into one table, and then compare them in a next load statement. I think you want to:

1. Load data into CSP from Excel

2. Left join data into CSP from CVF_T_MKT_IV_COMPANYDETAILS

3. Left join data into CSP from CVF_T_MKT_SEGMENTATION_ANALYSIS

4. Load data from CSP with your IF statement into a new table

But I'm not clear if the data in 2 and 3 should be concatenated or joined. 

[Tribhuwan]

Hi Lauri,

It worked.

Thanks a lot for your help😊