topic Re: Tables disappearing from the data model after a left join in Connectivity & Data Prep

Tables disappearing from the data model after a left join

Oggy172 — Wed, 17 Dec 2025 11:32:06 GMT

Strange one, I'm hoping someone can explain.

We have a load script, that builds a table (PurchaseOrders) and then joins some fields from that to a LINK table that was already established.

The same logic is applied for a table called PILData, but this isnt experiencing the issue.

Basically PurchaseOrders and PILData tables exist in the model until the very last block of code is ran, and then I get:

a) no additional data in LINK

b) PurchaseOrders table is blank

c) PILData is blank

Offending Block:

Full Script (impacted section)

let vTable = 'PurchaseOrderLines'; LIB CONNECT TO 'BC Testing - Odata:BC Odata Connection'; RestConnectorMasterTable: SQL SELECT "__KEY_root", (SELECT "@odata.etag", "Document_Type", "Document_No", "Line_No", "Buy_from_Vendor_No", "Buy_from_Vendor_Name", "Type", "No", "Variant_Code", "Description", "Description_2", "Location_Code", "Quantity", "Reserved_Qty_Base", "Unit_of_Measure_Code", "Direct_Unit_Cost", "Indirect_Cost_Percent", "Unit_Cost_LCY", "Unit_Price_LCY", "Line_Amount", "Order_No", "Allocation_Account_No", "Job_No", "Job_Task_No", "Job_Line_Type", "Shortcut_Dimension_1_Code", "Shortcut_Dimension_2_Code", "ShortcutDimCode_x005B_3_x005D_", "ShortcutDimCode_x005B_4_x005D_", "ShortcutDimCode_x005B_5_x005D_", "ShortcutDimCode_x005B_6_x005D_", "ShortcutDimCode_x005B_7_x005D_", "ShortcutDimCode_x005B_8_x005D_", "Expected_Receipt_Date", "Outstanding_Quantity", "Outstanding_Amount_LCY", "Amt_Rcd_Not_Invoiced_LCY", "Quantity_Invoiced", "Qty_Rcd_Not_Invoiced", "A_Rcd_Not_Inv_Ex_VAT_LCY", "Requested_Receipt_Date", "Order_Date", "__FK_value" FROM "value" FK "__FK_value") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION( URL "$(vBaseURL)/$(vTable)?$filter = Type eq 'G/L Account' and Document_Type eq 'Order' and Quantity gt 0", HTTPHEADER "Authorization" "Bearer $(vAccessToken)"); noconcatenate [PurchaseOrders]: LOAD // [@odata.etag], rowno() as orderid, [Document_Type] as [PO Document Type], [Document_No] as [PO No], [Line_No] as [Line No], [Buy_from_Vendor_No] as [PO Vendor No], [Buy_from_Vendor_Name] as [PO Vendor Name], [Type] as [PO Type], [No] as [PO GL Account No], [Variant_Code] as [Variant Code], [Description] as [PO Line Description], // [Description_2], [Location_Code] as [Location Code], [Quantity] as [PO Order Quantity], [Reserved_Qty_Base] as [Reserved Quantity (Base)], [Unit_of_Measure_Code] as [Unit of Measure Code], [Direct_Unit_Cost] as [Direct Unit Cost], [Indirect_Cost_Percent] as [Indirect Cost %], [Unit_Cost_LCY] as [Unit Cost (LCY)], [Unit_Price_LCY] as [Unit Price (LCY)], [Line_Amount] as [Line Amount], [Order_No] as [Order No], // [Allocation_Account_No], // [Job_No], // [Job_Task_No], // [Job_Line_Type], [Shortcut_Dimension_1_Code] as [PO Company], [Shortcut_Dimension_2_Code] as [PO Cost Centre], [ShortcutDimCode_x005B_3_x005D_] as [Shortcut Dimension 3 Code], [ShortcutDimCode_x005B_4_x005D_] as [Shortcut Dimension 4 Code], [ShortcutDimCode_x005B_5_x005D_] as [Shortcut Dimension 5 Code], [ShortcutDimCode_x005B_6_x005D_] as [Shortcut Dimension 6 Code], [ShortcutDimCode_x005B_7_x005D_] as [Shortcut Dimension 7 Code], [ShortcutDimCode_x005B_8_x005D_] as [Shortcut Dimension 8 Code], [Expected_Receipt_Date] as [Expected Receipt Date], [Outstanding_Quantity] as [Quantity Outstanding], [Outstanding_Amount_LCY] as [Amount Outstanding inc VAT (LCY)], [Amt_Rcd_Not_Invoiced_LCY] as [Amount Received, not Invoiced inc VAT (LCY)], [Quantity_Invoiced] as [Invoiced Quantity], [Qty_Rcd_Not_Invoiced] as [Quantity Received, not Invoiced], [A_Rcd_Not_Inv_Ex_VAT_LCY] as [Amount Received, not Invoiced excl VAT (LCY)], [Requested_Receipt_Date] as [Requested Receipt Date], [Order_Date] as [Order Date], [Shortcut_Dimension_2_Code]&'-'&[No] as OrderLink1, // added 101225 Date(today()) as impost // [Shortcut_Dimension_2_Code]&'-'&[No] as OrderLink // [__FK_value] AS [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_value]); DROP TABLE RestConnectorMasterTable; left join (PurchaseOrders) Load orderid, impost&'-'&[Order No]&'-'&OrderLink1 as datedorderlink resident PurchaseOrders; // exit script; LEFT JOIN (LINK) Load [PILData.Posting Date] as LINK_CALENDAR, [PILData.Shortcut Dimension 2 Code]&'-'&[PILData.No_] as OrderLink, [PILData.Order No_] as LINKPO, [PILData.Posting Date]&'-'&[PILData.Order No_]&'-'&[PILData.Shortcut Dimension 2 Code]&'-'&[PILData.No_] as datedorderlinkPO resident PILData; // The two tables exist at this point LEFT JOIN (LINK) Load impost as LINK_CALENDAR, OrderLink1 as OrderLink, [Order No] as LINKPO, impost&'-'&[Order No]&'-'&OrderLink1 as datedorderlink resident PurchaseOrders; exit script;

Re: Tables disappearing from the data model after a left join

Or — Wed, 17 Dec 2025 11:41:14 GMT

You're attempting to left-join when the fields already exist in the table. This is a different action - instead of adding fields, it'll try and find rows that match perfectly. Since no such rows exist, you wind up with the result you got.

Instead, try loading both blocks into a temporary table, and then left joining that temporary table to LINK.

Re: Tables disappearing from the data model after a left join

Oggy172 — Wed, 17 Dec 2025 12:17:47 GMT

I'll try that shortly, though Im not sure why that effectively purges my source/resident table?

Re: Tables disappearing from the data model after a left join

Oggy172 — Wed, 17 Dec 2025 13:31:33 GMT

Nope, it still does the same.

temptable is t

after concatenation from the two source tables, I have the correct data:

however if I continue through to the left join (LINK) the PurchaseOrder table is empty.

let vTable = 'PurchaseOrderLines'; LIB CONNECT TO 'BC Testing - Odata:BC Odata Connection'; RestConnectorMasterTable: SQL SELECT "__KEY_root", (SELECT "@odata.etag", "Document_Type", "Document_No", "Line_No", "Buy_from_Vendor_No", "Buy_from_Vendor_Name", "Type", "No", "Variant_Code", "Description", "Description_2", "Location_Code", "Quantity", "Reserved_Qty_Base", "Unit_of_Measure_Code", "Direct_Unit_Cost", "Indirect_Cost_Percent", "Unit_Cost_LCY", "Unit_Price_LCY", "Line_Amount", "Order_No", "Allocation_Account_No", "Job_No", "Job_Task_No", "Job_Line_Type", "Shortcut_Dimension_1_Code", "Shortcut_Dimension_2_Code", "ShortcutDimCode_x005B_3_x005D_", "ShortcutDimCode_x005B_4_x005D_", "ShortcutDimCode_x005B_5_x005D_", "ShortcutDimCode_x005B_6_x005D_", "ShortcutDimCode_x005B_7_x005D_", "ShortcutDimCode_x005B_8_x005D_", "Expected_Receipt_Date", "Outstanding_Quantity", "Outstanding_Amount_LCY", "Amt_Rcd_Not_Invoiced_LCY", "Quantity_Invoiced", "Qty_Rcd_Not_Invoiced", "A_Rcd_Not_Inv_Ex_VAT_LCY", "Requested_Receipt_Date", "Order_Date", "__FK_value" FROM "value" FK "__FK_value") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION( URL "$(vBaseURL)/$(vTable)?$filter = Type eq 'G/L Account' and Document_Type eq 'Order' and Quantity gt 0", HTTPHEADER "Authorization" "Bearer $(vAccessToken)"); noconcatenate [PurchaseOrders]: LOAD // [@odata.etag], rowno() as orderid, [Document_Type] as [PO Document Type], [Document_No] as [PO No], [Line_No] as [Line No], [Buy_from_Vendor_No] as [PO Vendor No], [Buy_from_Vendor_Name] as [PO Vendor Name], [Type] as [PO Type], [No] as [PO GL Account No], [Variant_Code] as [Variant Code], [Description] as [PO Line Description], // [Description_2], [Location_Code] as [Location Code], [Quantity] as [PO Order Quantity], [Reserved_Qty_Base] as [Reserved Quantity (Base)], [Unit_of_Measure_Code] as [Unit of Measure Code], [Direct_Unit_Cost] as [Direct Unit Cost], [Indirect_Cost_Percent] as [Indirect Cost %], [Unit_Cost_LCY] as [Unit Cost (LCY)], [Unit_Price_LCY] as [Unit Price (LCY)], [Line_Amount] as [Line Amount], [Order_No] as [Order No], // [Allocation_Account_No], // [Job_No], // [Job_Task_No], // [Job_Line_Type], [Shortcut_Dimension_1_Code] as [PO Company], [Shortcut_Dimension_2_Code] as [PO Cost Centre], [ShortcutDimCode_x005B_3_x005D_] as [Shortcut Dimension 3 Code], [ShortcutDimCode_x005B_4_x005D_] as [Shortcut Dimension 4 Code], [ShortcutDimCode_x005B_5_x005D_] as [Shortcut Dimension 5 Code], [ShortcutDimCode_x005B_6_x005D_] as [Shortcut Dimension 6 Code], [ShortcutDimCode_x005B_7_x005D_] as [Shortcut Dimension 7 Code], [ShortcutDimCode_x005B_8_x005D_] as [Shortcut Dimension 8 Code], [Expected_Receipt_Date] as [Expected Receipt Date], [Outstanding_Quantity] as [Quantity Outstanding], [Outstanding_Amount_LCY] as [Amount Outstanding inc VAT (LCY)], [Amt_Rcd_Not_Invoiced_LCY] as [Amount Received, not Invoiced inc VAT (LCY)], [Quantity_Invoiced] as [Invoiced Quantity], [Qty_Rcd_Not_Invoiced] as [Quantity Received, not Invoiced], [A_Rcd_Not_Inv_Ex_VAT_LCY] as [Amount Received, not Invoiced excl VAT (LCY)], [Requested_Receipt_Date] as [Requested Receipt Date], [Order_Date] as [Order Date], [Shortcut_Dimension_2_Code]&'-'&[No] as OrderLink1, // added 101225 Date(today()) as impost // [Shortcut_Dimension_2_Code]&'-'&[No] as OrderLink // [__FK_value] AS [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_value]); DROP TABLE RestConnectorMasterTable; left join (PurchaseOrders) Load orderid, impost&'-'&[Order No]&'-'&OrderLink1 as datedorderlink resident PurchaseOrders; // exit script; qualify * ; t: Load [PILData.Posting Date] as LINK_CALENDAR, [PILData.Shortcut Dimension 2 Code]&'-'&[PILData.No_] as OrderLink, [PILData.Order No_] as LINKPO, [PILData.Posting Date]&'-'&[PILData.Order No_]&'-'&[PILData.Shortcut Dimension 2 Code]&'-'&[PILData.No_] as datedorderlinkPO resident PILData; unqualify * ; concatenate(t) Load impost as t.LINK_CALENDAR, OrderLink1 as t.OrderLink, [Order No] as t.LINKPO, impost&'-'&[Order No]&'-'&OrderLink1 as t.datedorderlink resident PurchaseOrders; left join (LINK) load t.LINK_CALENDAR as LINK_CALENDAR, t.OrderLink as OrderLink, t.LINKPO as LINKPO, t.datedorderlinkPO as datedorderlinkPO, t.datedorderlink as datedorderlink resident t; drop table t; exit script;

Re: Tables disappearing from the data model after a left join

Or — Wed, 17 Dec 2025 14:13:09 GMT

In the above code, I don't see any common fields between LINK and t, so a left join wouldn't do anything? You've commented out the only common field...

[Edit] Come to think of it, I don't see a LINK table at all? I was looking at PurchaseOrders for the join fields

Re: Tables disappearing from the data model after a left join

Oggy172 — Wed, 17 Dec 2025 16:01:15 GMT

Yes, I said it was already established.

unqualify * ; LINK: Load [Posting Date] as LINK_CALENDAR, CostCentre as LINK_CC, Text(GLCode) as LINK_GL, CostCentre&'-'&Text(GLCode) as OrderLink, Company as NavCo, OhId Resident OH; //exit script LEFT JOIN(LINK) LOAD LINK_CALENDAR, FiscalYear&'-'&FiscalPeriod as FYPER, FiscalYear&'-'&Right('0'&FiscalPeriod,2) as HDFYPER Resident Calendar; //exit Script LEFT JOIN (LINK) LOAD DISTINCT OhId, HDFYPER&'-'&LINK_CC as LINK_HEADS Resident LINK;

Re: Tables disappearing from the data model after a left join

Or — Thu, 18 Dec 2025 09:02:44 GMT

Lacking the actual data and with nothing glaringly obvious wrong, I would suggest replacing the left join with a left keep, then removing key fields one at a time until you actually get data on both sides (which would of course not be correct). That should point you at the right issue. I would also suggest confirming all of the join fields share the same type on both sides, as having one value numeric and one textual will break the join.

Re: Tables disappearing from the data model after a left join

marcus_sommer — Thu, 18 Dec 2025 13:20:43 GMT

Without diving deeper in your issues with the joins a link-table couldn't be created per joins else only with a concatenation. If tables have a perfect relation to each other you wouldn't need a bridge else could associate them directly but if there are any missing keys it needs measurements tor ensure that these records exists.

Beside this is a link-table quite often a detour and it would save efforts to concatenate the facts directly.

Re: Tables disappearing from the data model after a left join

marcus_sommer — Thu, 18 Dec 2025 13:20:53 GMT

Beside this is a link-table quite often a detour and it would save efforts to concatenate the facts directly.