Concatenate/LinkTable Help

[fkskirer]

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]);

[skamath1]

Add Qualify to the field list that you do not want to create the synthetic keys between the tables.

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegu...

Also you can create key value for set of fields using the autonumber script function to avoid creating synthetic key.

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterFun...

[marcus_sommer · Accepted solution]

It depends on how the data are related to eachother which approach to merge and/or to associate them might be the most suitable.

Concatenating the open/close tables with the same data-structure is in each case the right one to start. Without knowing your data and requirements it's difficult to go further but just by looking on the table-fields I could imagine that a joining/mapping of the audit-data to the lines- and/or the submission-data might useful. After that the remaining two tables could be associated by a KEY (one or maybe several fields) and the remaining identical fieldnames will be just renamed.

- Marcus

[fkskirer]

Thanks Marcus!