Join Node fails while running keys on long text string values

Hi all,
I was trying to join 2 tables with the below mentioned keys.
I pasted the list of keys below.

Total Recordable Cases (TRCs)
Loss of Primary Containment (LOPC’s) >100 kg
Customer Quality Incidents - Major
Customer Quality Incidents - Minor
Customer OTIF - Direct
Customer OTIF - Indirect
Customer OTIF - Consolidated
Customer Satisfaction Index [CSI]
Delivery Customer Satisfaction Index [CSI]
Demand Forecast Accuracy (%)
Bias in Control (%)
Bias (%)
Overall Equipment Effectiveness [OEE] (%)-Lubes
Stock under Block location (mL)
BCI Success Rate (C3)
BCI Success Rate (Count)
Non Performing SKUs ( Sales >12 months, Branded)
Ops TTM (% on time) - Excluding GKA SKUs
Days Inventory Outstanding (Days)
Stock Value (USD mil)
Days Payable Outstanding (Days)
Production Volumes (ML)
Delivered Volume (ML)
Supply Volume (ML)
Sales Volume - CGM + PO (ML)
Sales Volume - CGM + PO + Marine + Retail + Others (ML)
Premium Volumes
Total Opex incl. Lease & RE (excl Opex 4 Capex) - M$
Total Opex (excl Opex 4 Capex) - M$
Total Accountable Opex - M$
Total Opex 4 Capex - M$
Leases - Total Opex
Leases - Plant
Leases - Logistic
Real Estate Costs
Total Plant Opex (M$)
Logistics Opex (excl. Export Freight) -M$
Integrated C5 (Reported) (M$)
LSC C5 (M$)
Sales C5 GL (M$)
Premium C3
Capex Spend (AI & MM + Growth)
Unit Opex (excl Opex 4 Capex) - cpl
Unit Opex (excl Opex 4 Capex) - cpl (incl leases & RE)
Unit Accountable Opex - cpl
Unit Plant Opex cpl - (incl leases & RE)
Unit Logistics (excl. Export Freight) - cpl (incl leases)
Sales Unit COGS (COGS + Opex) cpl
Unit Proceeds (Cpl)
Unit C3 (Cpl)
Tail stock (Dead & At Risk + BCW) - M$ (saving)
War of Waste (excl. Tail Stock) - M$ (Spend)
AG Cost (M$)
Shell FTEs - non-SCME
Shell FTEs - SCME
Production CO2 avoided (tonnes CO2e)
Logistics CO2 avoided (tonnes CO2e)
Packaging CO2 avoided (tonnes CO2e)

However, when I run the join node, it works fine for almost all combinations except 5 categories pasted below which do not get joined and are reported as unmatched. This issue was also detected while running the join node in Alteryx. I am unable to understand whether this is due to the long text key string values or any other issue.

Total Plant Opex (M$)
Total Opex (excl Opex 4 Capex) - M$
Total Opex 4 Capex - M$
Total Accountable Opex - M$
Total Opex incl. Lease & RE (excl Opex 4 Capex) - M$

One set of tables which contains the key is derived within the workflow. I then, copied the above set of keys into an excel file along with additional data and tried to join the tables on the above keys to bring the additional data columns into the workflow. That is where the join keeps on failing.

If I simply copy paste the list of key values normally into two separate table creator nodes and then run Join, I do not get the join mismatch.

I would also be keen to check if there are any other possible approaches to manage such situations in a different manner.

Any help on this matter is highly appreciated.
Thanks in advance.

@nilotpalc in my example the text would match. Could you provide an example where this problem would occur?

kn_forum_48440_join_long_texts.knwf (48.9 KB)

Hi @mlauber71
Thanks for the quick check.
I am attaching a file as test data comprising of the 2 worksheets. I have been facing this issue when I try to join sheet1 data and sheet2 data on the KPI Categories field.

Test data.xlsx (14.0 KB)

JoinNode_Fails with Data

Hi @mlauber71
I am not sure how to upload the workflow on the forum and hence, sharing the link on the public hub.
Thanks, once again.

@nilotpalc it seems there is an additional blank in your strings … once you remove that everything would work …


kn_forum_48440_join_long_texts.knwf (221.1 KB)


Thanks for the insight. I never realized that the copy-paste activities between datasets and excel resulted in an extra whitespace.
Thanks a ton for sharing the solution. Really helpful.

1 Like

To be honest I very much doubt that … great that you found the problem


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.