Hello Everyone,
I am new to Qlik Sense and need your help.
I have 2 tables CSP and Segment and both tables have 5 common columns:
FUP_ID
FUP_NAME
GCN_ID
GCN_Name
GCN_ID_TYPE
CSP table has the latest and most updated FUP_ID and FUP_Name, where as Segment table has updated GCN_ID and GCN_Name
Now I want to join them in such a way that the GCN_ID and GCN_Name should be replaced with FUP_ID and FUP_Name where they are not matched and wherever FUP_ID column is not blank.
I have tried it getting the below output (which is wrong):
| IVCOMPANYID | IVNAME | FUP_ID | FUP_NAME | GCN_ID | GCN_Name | GCN_ID_TYPE |
| 1031752 | Orchard Information Systems Limited | 962419 | MRI Software LLC | 1031752 | Orchard Information Systems Limited | IV ID |
| 1038300 | ZKW Zizala Lichtsysteme GmbH | 816526 | LG Corp | 1038300 | Zizala Lichtsysteme GmbH | IV ID |
| 1039052 | King's College London | 15029 | University Of London | 61658933 | Inspired Education Holdings Limited | IV ID |
| 50509353 | Candy Hoover Gmbh | 4077389 | Haier Group Corporation | 1053829 | Candy Hoover Group Srl | IV ID |
| 62365458 | Rip Curl | 1226175 | Rip Curl Group Pty Ltd | 2521048 | Kathmandu Holdings Ltd | IV ID |
| 50220034 | SLB 2020 Limited | 50220034 | SLB 2020 Limited | 10897469 | Greybull Capital LLP | IV ID |
| 1100724 | Lightstone Value Plus Real Estate Investment Trust Inc. | 1100724 | Lightstone Value Plus Real Estate Investment Trust Inc. | 21235 | The Lightstone Group LLC | IV ID |
| 1152617 | FFastFill Inc. | 10870183 | ION Investment Group Limited | 1152617 | FFastFill Inc. | IV ID |
| 1379535 | Hafslund USA, Inc. | 61622666 | Hafslund E-Co AS | 861489 | Hafslund ASA | IV ID |
My desired output should look like the one given below (where FUP_ID and GCN_ID are same):
| IVCOMPANYID | IVNAME | FUP_ID | FUP_NAME | GCN_ID | GCN_Name | GCN_ID_TYPE |
| 1031752 | Orchard Information Systems Limited | 962419 | MRI Software LLC | 962419 | MRI Software LLC | IV ID |
| 1038300 | ZKW Zizala Lichtsysteme GmbH | 816526 | LG Corp | 816526 | LG Corp | IV ID |
| 1039052 | King's College London | 15029 | University Of London | 15029 | University Of London | IV ID |
Below is my script:
CSP:
LOAD
S.no.,
"Account Name",
"DiscoverOrg ID",
IVCOMPANYID,
GCN_ID,
GCN_NAME,
GCN_ID_TYPE
FROM [lib://Excel File Location (internal_tsingh1)/Qlik Test Data.xlsx]
(ooxml, embedded labels, table is Sheet2);
Segment:
Left Join(CSP)
LOAD
"FUP_ID",
"FUP_NAME",
if(GCN_ID<>FUP_ID or not isnull(FUP_ID),FUP_ID,GCN_ID) as GCN_ID,
"GCN_NAME",
"GCN_ID_TYPE",
"IVEMPLOYEES",
"IVINDUSTRY";
SQL SELECT "GCN_ID",
"GCN_NAME",
"GCN_ID_TYPE",
"FUP_ID",
"FUP_NAME",
"IVEMPLOYEES",
"IVINDUSTRY"
FROM "EDWC_CAMPAIGNS"."CVF_T_MKT_IV_COMPANYDETAILS";
Could anyone of you please guide me how can it be done and what I am doing wrong here.
