Tables disappearing from the data model after a le... - Qlik Community

Oggy172 · ‎2025-12-17

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;

Or · ‎2025-12-17

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.

Oggy172 · ‎2025-12-17

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

Oggy172 · ‎2025-12-17

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;

Or · ‎2025-12-17

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

Oggy172 · ‎2025-12-17

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;

Or · ‎2025-12-18

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.

marcus_sommer · ‎2025-12-18

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.

marcus_sommer · ‎2025-12-18

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.

Tables disappearing from the data model after a left join

Data Prep