6 Replies Latest reply: May 24, 2017 2:41 AM by Peter Cammaert RSS

    Multiple joins in Data Load Editor

    Emmanuel Tatipatri

      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
          Michele De Nardi

          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
              Emmanuel Tatipatri

              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

                • Re: Multiple joins in Data Load Editor
                  Emmanuel Tatipatri

                  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

              • Re: Multiple joins in Data Load Editor
                Peter Cammaert

                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?