Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
fkskirer
Contributor II
Contributor II

Concatenate/LinkTable Help

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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

3 Replies
skamath1
Creator III
Creator III

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

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
Contributor II
Contributor II
Author

Thanks Marcus!