Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple joins in Data Load Editor

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

6 Replies
micheledenardi
Specialist II
Specialist II

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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)EmptyCOlumns.jpg

Not applicable
Author

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

ServicePort.jpg

micheledenardi
Specialist II
Specialist II

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.

sql-joins.png

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
prma7799
Master III
Master III

Otherwise do the concatenate with flags

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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?