topic Re: Multiple joins in Data Load Editor in QlikView

Multiple joins in Data Load Editor

Tue, 23 May 2017 15:17:55 GMT

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!!

Re: Multiple joins in Data Load Editor

micheledenardi — Tue, 23 May 2017 15:41:44 GMT

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

Re: Multiple joins in Data Load Editor

Tue, 23 May 2017 16:06:23 GMT

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)

Re: Multiple joins in Data Load Editor

Tue, 23 May 2017 16:09:39 GMT

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

Re: Multiple joins in Data Load Editor

micheledenardi — Wed, 24 May 2017 06:32:04 GMT

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.

Re: Multiple joins in Data Load Editor

prma7799 — Wed, 24 May 2017 06:40:49 GMT

Otherwise do the concatenate with flags

Re: Multiple joins in Data Load Editor

Peter_Cammaert — Wed, 24 May 2017 06:40:58 GMT

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?