Hi Qlik Gurus,
I've been stuck for a few days trying to connect some data sets together. I have 6 tables named: Open Lines, Closed Lines, Open Submissions, Closed Submissions, Open Audit History and Closed Audit History.
The corresponding open/closed tables have the exact same fields and I was able to successfully concatenate those tables together so now I only have 3 tables. Lines, Submissions, and Audit History. But now I am stuck trying to link the tables together and avoid synthetic keys as there are multiple fields in common between them. My thought process was to first concatenate the open/closed tables together- and then use a link table to bring them together after the concatenation but with no success. Maybe there is a better approach? Any help would be greatly appreciated
Here is my script below: I've also attached a screenshot of the data model viewer which helps identify the common fields
[Lines]:
LOAD
[RUN_ID],
[REF_NUM],
[LIFECYCLE_BATCH_NUM],
[REF_ID],
[EXTERNAL_REF_ID],
[SALES_SUB_ID],
[RECEIVED_DATE],
[LINE_REF_NUM],
[EXTERNAL_ITEM_ID],
[SHIP_TO_CUST_ID],
[SHIP_TO_CUST_NUM],
[CONTRACT_AMT],
[TOTAL_CONTRACT_AMT],
[EXTERNAL_CONTRACT_ID],
Date(Date#([EXTERNAL_INV_DATE], 'MM/DD/YYYY') ) AS [EXTERNAL_INV_DATE],
[SHIP_TO_CUST_NAME],
[SHIP_TO_CUST_ZIP],
[SHIP_TO_CUST_COUNTRY],
[EXTERNAL_INV_AMT],
[EXTERNAL_INVOICE_QTY],
[EXTERNAL_INV_UOM],
[INV_AMT],
[ORIGINAL_INV_QTY],
[ORIGINAL_INV_UOM],
[INV_QTY],
[INV_UOM],
[TOTAL_INV_AMT],
[SRC_MODEL_DATE],
[REVERSAL_STATUS],
[BACKWARD_REF_ID],
[BACKWARD_ROOT_REF_ID],
[FORWARD_REF_ID],
[LIFECYCLE_STATUS],
[COMMENTS],
[EXT_COMMENT],
[SALE_DOMAIN],
[RESUB_TYPE],
[EXTERN_PAR_DISTR_DEBIT_MEMO],
[EXTERNAL_BRANCH_DISTR_NAME],
[EXTERN_BRANCH_DISTR_BRANCH_ID],
[EXTERN_BRANCH_DISTR_DEBIT_MEMO],
[EXTERNAL_DISTR_COST_AMT],
[DISTR_COST_AMT],
[TOTAL_DISTR_COST_AMT],
[EXTERNAL_CONTRACT_AMT],
[CHGBK_AMT],
[PAID_CHGBK_AMT],
[TOTAL_CHGBK_AMT],
[EXTERNAL_CHGBK_AMT],
[EXTERNAL_TOTAL_CHGBK_AMT],
[OVERRIDE_CONTRACT],
[OVERRIDE_BRANCH],
[OVERRIDE_CUSTOMER],
[OVERRIDE_PRODUCT],
[OVERRIDE_BASE_QTY],
[OVERRIDE_TOTAL_DISTR_COST_AMT],
[OVERRIDE_UOM_CODE],
[TOTAL_DISCREPANCY_AMT] as [LINE_TOTAL_DISCREPANCY_AMT],
[COMMENT_TO_WHOLESALER],
[DATE_CREATED],
[OVERRIDE_INV_DATE_FLAG],
[EXT_ORIGINAL_CONTRACT_ID],
[SHIP_TO_EXT_CUST1],
[SHIP_TO_EXT_CUST3],
[SHIP_TO_EXT_CUST2],
[SHIP_TO_EXT_CUST4],
[EXTERNAL_RESUB_TYPE]
FROM [lib://Server Qlik Upload Files (app_nt_sgueorguiev)/Chargebacks\Closed Lines.xlsx]
(ooxml, embedded labels, table is [Closed Lines]);
[Open Lines]:
Concatenate ([Lines])
LOAD
[RUN_ID],
[REF_NUM],
[LIFECYCLE_BATCH_NUM],
[REF_ID],
[EXTERNAL_REF_ID],
[SALES_SUB_ID],
[RECEIVED_DATE],
[LINE_REF_NUM],
[EXTERNAL_ITEM_ID],
[SHIP_TO_CUST_ID],
[SHIP_TO_CUST_NUM],
[CONTRACT_AMT],
[TOTAL_CONTRACT_AMT],
[EXTERNAL_CONTRACT_ID],
Date(Date#([EXTERNAL_INV_DATE], 'MM/DD/YYYY') ) AS [EXTERNAL_INV_DATE],
[SHIP_TO_CUST_NAME],
[SHIP_TO_CUST_ZIP],
[SHIP_TO_CUST_COUNTRY],
[EXTERNAL_INV_AMT],
[EXTERNAL_INVOICE_QTY],
[EXTERNAL_INV_UOM],
[INV_AMT],
[ORIGINAL_INV_QTY],
[ORIGINAL_INV_UOM],
[INV_QTY],
[INV_UOM],
[TOTAL_INV_AMT],
[SRC_MODEL_DATE],
[REVERSAL_STATUS],
[BACKWARD_REF_ID],
[BACKWARD_ROOT_REF_ID],
[FORWARD_REF_ID],
[LIFECYCLE_STATUS],
[COMMENTS],
[EXT_COMMENT],
[SALE_DOMAIN],
[RESUB_TYPE],
[EXTERN_PAR_DISTR_DEBIT_MEMO],
[EXTERNAL_BRANCH_DISTR_NAME],
[EXTERN_BRANCH_DISTR_BRANCH_ID],
[EXTERN_BRANCH_DISTR_DEBIT_MEMO],
[EXTERNAL_DISTR_COST_AMT],
[DISTR_COST_AMT],
[TOTAL_DISTR_COST_AMT],
[EXTERNAL_CONTRACT_AMT],
[CHGBK_AMT],
[PAID_CHGBK_AMT],
[TOTAL_CHGBK_AMT],
[EXTERNAL_CHGBK_AMT],
[EXTERNAL_TOTAL_CHGBK_AMT],
[OVERRIDE_CONTRACT],
[OVERRIDE_BRANCH],
[OVERRIDE_CUSTOMER],
[OVERRIDE_PRODUCT],
[OVERRIDE_BASE_QTY],
[OVERRIDE_TOTAL_DISTR_COST_AMT],
[OVERRIDE_UOM_CODE],
[TOTAL_DISCREPANCY_AMT] as [LINE_TOTAL_DISCREPANCY_AMT],
[COMMENT_TO_WHOLESALER],
[DATE_CREATED],
[OVERRIDE_INV_DATE_FLAG],
[EXT_ORIGINAL_CONTRACT_ID],
[SHIP_TO_EXT_CUST1],
[SHIP_TO_EXT_CUST3],
[SHIP_TO_EXT_CUST2],
[SHIP_TO_EXT_CUST4],
[EXTERNAL_RESUB_TYPE]
FROM [lib://Server Qlik Upload Files (app_nt_sgueorguiev)/Chargebacks\Open Lines.xlsx]
(ooxml, embedded labels, table is [Open Lines]);
[Submissions]:
LOAD
[SALES_SUB_ID],
[RUN_ID],
[REF_NUM],
[LIFECYCLE_BATCH_NUM],
[REF_ID],
[EXT_BRANCH_DISTR_ID],
[EXT_DEBIT_MEMO_ID],
[RECEIVED_DATE],
[CLOSE_DATE],
[SALE_DOMAIN],
[REVERSAL_STATUS],
[SUBMISSION_STATUS],
[PRE_SUBMISSION_STATUS],
[RESUB_TYPE],
[DIST_REBATE_CLAIM_ID],
[DATE_CREATED],
[LINE_COUNT],
[TOTAL_DISCREPANCY_AMT] As [SUBMISSION_TOTAL_DISCREPANCY_AMT],
[TOTAL_DISTR_REBATE_AMT],
[PARENT_REF_ID]
FROM [lib://Server Qlik Upload Files (app_nt_sgueorguiev)/Chargebacks\Closed Submissions.xlsx]
(ooxml, embedded labels, table is [Closed Submissions]);
[Open Submissions]:
Concatenate ([Submissions])
LOAD
[SALES_SUB_ID],
[RUN_ID],
[REF_NUM],
[LIFECYCLE_BATCH_NUM],
[REF_ID],
[EXT_BRANCH_DISTR_ID],
[EXT_DEBIT_MEMO_ID],
[RECEIVED_DATE],
[CLOSE_DATE],
[SALE_DOMAIN],
[REVERSAL_STATUS],
[SUBMISSION_STATUS],
[PRE_SUBMISSION_STATUS],
[RESUB_TYPE],
[DIST_REBATE_CLAIM_ID],
[DATE_CREATED],
[LINE_COUNT],
[TOTAL_DISCREPANCY_AMT] As [SUBMISSION_TOTAL_DISCREPANCY_AMT],
[TOTAL_DISTR_REBATE_AMT],
[PARENT_REF_ID]
FROM [lib://Server Qlik Upload Files (app_nt_sgueorguiev)/Chargebacks\Open Submissions.xlsx]
(ooxml, embedded labels, table is [Open Submissions]);
[Audit History]:
LOAD
AUDIT_MSG,
SALES_SUB_ID,
LINE_REF_NUMS,
DATE_CREATED as [ACTIVITY_TIME_STAMP],
MEMBER_ID_CREATED
FROM [lib://Server Qlik Upload Files (app_nt_sgueorguiev)/Chargebacks\Open Audit History.xlsx]
(ooxml, embedded labels, table is [Open Audit History]);
[Closed Audit History]:
Concatenate ([Audit History])
LOAD
AUDIT_MSG,
SALES_SUB_ID,
LINE_REF_NUMS,
DATE_CREATED as [Activity Time Stamp],
MEMBER_ID_CREATED
FROM [lib://Server Qlik Upload Files (app_nt_sgueorguiev)/Chargebacks\Closed Audit History.xlsx]
(ooxml, embedded labels, table is [Closed Audit History]);
