Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik Community,
I am using the Qlik REST Connector to pull data from our Service Now instance and the script generated in the connector is creating separate tables for all the look up values in the main table. something like below:
RestConnectorMasterTable:
SQL SELECT
"owned_by",
"company",
"__KEY_result",
(SELECT
"display_value",
"link",
"__FK_parent"
FROM "parent" FK "__FK_parent"),
(SELECT
"display_value" AS "display_value_u0",
"link" AS "link_u0",
"__FK_owned_by"
FROM "owned_by" FK "__FK_owned_by"),
(SELECT
"display_value" AS "display_value_u1",
"link" AS "link_u1",
"__FK_support_group"
FROM "support_group" FK "__FK_support_group"),
(SELECT
"display_value" AS "display_value_u2",
"link" AS "link_u2",
"__FK_sys_domain"
FROM "sys_domain" FK "__FK_sys_domain"),
(SELECT
"display_value" AS "display_value_u3",
"link" AS "link_u3",
"__FK_company"
FROM "company" FK "__FK_company"),
(SELECT
"display_value" AS "display_value_u4",
"link" AS "link_u4",
"__FK_model_id"
FROM "model_id" FK "__FK_model_id"),
(SELECT
"display_value" AS "display_value_u5",
"link" AS "link_u5",
"__FK_vendor"
FROM "vendor" FK "__FK_vendor"),
(SELECT
"display_value" AS "display_value_u6",
"link" AS "link_u6",
"__FK_u_service_executive"
FROM "u_service_executive" FK "__FK_u_service_executive"),
(SELECT
"display_value" AS "display_value_u7",
"link" AS "link_u7",
"__FK_managed_by"
FROM "managed_by" FK "__FK_managed_by"),
(SELECT
"display_value" AS "display_value_u8",
"link" AS "link_u8",
"__FK_u_service_portfolio"
FROM "u_service_portfolio" FK "__FK_u_service_portfolio"),
(SELECT
"display_value" AS "display_value_u9",
"link" AS "link_u9",
"__FK_u_functional_service_lead"
FROM "u_functional_service_lead" FK "__FK_u_functional_service_lead"),
(SELECT
"display_value" AS "display_value_u10",
"link" AS "link_u10",
"__FK_u_support_company"
FROM "u_support_company" FK "__FK_u_support_company"),
(SELECT
"display_value" AS "display_value_u11",
"link" AS "link_u11",
"__FK_u_service_portfolio_exec"
FROM "u_service_portfolio_exec" FK "__FK_u_service_portfolio_exec"),
(SELECT
"display_value" AS "display_value_u12",
"link" AS "link_u12",
"__FK_u_service_architect"
FROM "u_service_architect" FK "__FK_u_service_architect"),
(SELECT
"display_value" AS "display_value_u13",
"link" AS "link_u13",
"__FK_change_control"
FROM "change_control" FK "__FK_change_control"),
(SELECT
"display_value" AS "display_value_u14",
"link" AS "link_u14",
"__FK_location"
FROM "location" FK "__FK_location"),
(SELECT
"display_value" AS "display_value_u15",
"link" AS "link_u15",
"__FK_u_service_owner"
FROM "u_service_owner" FK "__FK_u_service_owner")
FROM JSON (wrap off) "result" PK "__KEY_result";
Service_parent:
LOAD [display_value] AS [parent],
[link] AS [link],
[__FK_parent] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_parent]);
Service_owned_by:
LOAD [display_value_u0] AS [owned_by],
[link_u0] AS [link_u0],
[__FK_owned_by] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_owned_by]);
Service_support_group:
LOAD [display_value_u1] AS [support_group],
[link_u1] AS [link_u1],
[__FK_support_group] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_support_group]);
// [sys_domain]:
// LOAD [display_value_u2] AS [sys_domain],
// [link_u2] AS [link_u2],
// [__FK_sys_domain] AS [__KEY_result]
// RESIDENT RestConnectorMasterTable
// WHERE NOT IsNull([__FK_sys_domain]);
I am trying to figure out a way to join all of these tables together and load it into a QVD. Can someone please help with the syntax to join multiple tables in the data load editor?
Service:
LOAD
owned_by,
company,
__KEY_result,
1 as ServiceTable
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service.qvd]
(qvd);
Inner Join
LOAD
link_u8,
__KEY_result,
u_service_portfolio As service_portfolio
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_service_portfolio.qvd]
(qvd);
Inner Join
LOAD
u_support_company AS u_support_company,
link_u10,
__KEY_result
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_support_company.qvd]
(qvd);
Inner Join
LOAD
u_service_portfolio_exec as u_service_portfolio_exec,
link_u11,
__KEY_result
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_service_portfolio_exec.qvd]
(qvd);
Inner Join
LOAD
_service_executive as u_service_executive,
link_u6,
__KEY_result
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_service_executive.qvd]
(qvd);
Inner Join
LOAD
u_service_architect as u_service_architect,
link_u12,
__KEY_result
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_service_architect.qvd]
(qvd);
The first inner join works, but when I include the rest of the joins, it doesn't.
Thanks!!
For each join, try to write the destination table...
Inner join(Service)
Load
u_support_company AS u_support_company,
link_u10,
__KEY_result
from FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_support_company.qvd] (qvd);
Hi Michele,
Thanks for the response.
I just tried it as below:
Service:
LOAD
operational_status,
sys_updated_on,
u_foundational_service,
u_vendor_feed_id,
discovery_source,
first_discovered,
u_exception,
due_in,
used_for,
gl_account,
invoice_number,
sys_created_by,
warranty_expiration,
u_disposal_feed_id,
u_service_executive,
sla,
u_financial,
checked_out,
sys_domain_path,
version,
u_service_name,
maintenance_schedule,
cost_center,
dns_domain,
assigned,
u_technology_function,
purchase_date,
short_description,
busines_criticality,
managed_by,
u_out_for_disposal_feed_id,
can_print,
last_discovered,
sys_class_name,
manufacturer,
u_tier_info,
vendor,
cfg_auto_management_server,
model_number,
assigned_to,
start_date,
u_service_portfolio,
u_functional_service_lead,
ng_assignment_flag,
serial_number,
u_last_import_feed_id,
price_unit,
correlation_id,
unverified,
attributes,
asset,
u_reputation,
u_service_type,
u_operational,
skip_sync,
u_support_company,
sys_updated_by,
u_service_portfolio_exec,
sys_created_on,
u_received_feed_id,
install_date,
asset_tag,
user_group,
fqdn,
u_legal___regulatory,
u_tracc_onboarded_date,
change_control,
unit_description,
delivery_date,
install_status,
name,
subcategory,
price_model,
u_service_architect,
sys_id,
po_number,
checked_in,
mac_address,
justification,
department,
comments,
cost,
cfg_auto_change,
sys_mod_count,
monitor,
ip_address,
sys_tags,
cost_cc,
order_date,
schedule,
due,
location,
u_service_owner,
category,
fault_count,
change_request,
lease_id,
service_classification,
support_group,
model_id,
parent,
owned_by,
company,
__KEY_result,
name as [Service Name],
sys_id as svc_rel,
u_service_portfolio as svc_service_portfolio,
1 as ServiceTable
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service.qvd]
(qvd);
Inner Join (Service)
LOAD
link_u8,
__KEY_result,
u_service_portfolio As service_portfolio
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_service_portfolio.qvd]
(qvd);
Inner Join (Service)
LOAD
u_support_company AS u_support_company,
link_u10,
__KEY_result
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_support_company.qvd]
(qvd);
But when I look for the data, both the columns are blank (Attached)
Here's what I see when I comment out the second join :
Service:
LOAD
operational_status,
sys_updated_on,
u_foundational_service,
u_vendor_feed_id,
discovery_source,
first_discovered,
u_exception,
due_in,
used_for,
gl_account,
invoice_number,
sys_created_by,
warranty_expiration,
u_disposal_feed_id,
u_service_executive,
sla,
u_financial,
checked_out,
sys_domain_path,
version,
u_service_name,
maintenance_schedule,
cost_center,
dns_domain,
assigned,
u_technology_function,
purchase_date,
short_description,
busines_criticality,
managed_by,
u_out_for_disposal_feed_id,
can_print,
last_discovered,
sys_class_name,
manufacturer,
u_tier_info,
vendor,
cfg_auto_management_server,
model_number,
assigned_to,
start_date,
u_service_portfolio,
u_functional_service_lead,
ng_assignment_flag,
serial_number,
u_last_import_feed_id,
price_unit,
correlation_id,
unverified,
attributes,
asset,
u_reputation,
u_service_type,
u_operational,
skip_sync,
u_support_company,
sys_updated_by,
u_service_portfolio_exec,
sys_created_on,
u_received_feed_id,
install_date,
asset_tag,
user_group,
fqdn,
u_legal___regulatory,
u_tracc_onboarded_date,
change_control,
unit_description,
delivery_date,
install_status,
name,
subcategory,
price_model,
u_service_architect,
sys_id,
po_number,
checked_in,
mac_address,
justification,
department,
comments,
cost,
cfg_auto_change,
sys_mod_count,
monitor,
ip_address,
sys_tags,
cost_cc,
order_date,
schedule,
due,
location,
u_service_owner,
category,
fault_count,
change_request,
lease_id,
service_classification,
support_group,
model_id,
parent,
owned_by,
company,
__KEY_result,
name as [Service Name],
sys_id as svc_rel,
u_service_portfolio as svc_service_portfolio,
1 as ServiceTable
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service.qvd]
(qvd);
Inner Join (Service)
LOAD
link_u8,
__KEY_result,
u_service_portfolio As service_portfolio
FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_service_portfolio.qvd]
(qvd);
// Inner Join (Service)
// LOAD
// u_support_company AS u_support_company,
// link_u10,
// __KEY_result
// FROM [lib://CMDB_Extract (aonnet_sdlqlik)/E_Service_u_support_company.qvd]
// (qvd);
This is because the two tables have no common key records, so you have to change your "Inner join(Service)" with another type of join according to your purpose.
Otherwise do the concatenate with flags
All of those resident tables originate in a Master table created by the Rest Connectors complex nested SELECT statement. Can't you just store that Master table into a QVD instead of first pulling apart the subsets and then joining them back together again?