How to link 2 tables in Qliksense pulled from ServiceNow

Deanbrian1976 — Wed, 17 Apr 2019 21:00:53 GMT

Hello,

I am trying to link a "VIP" field which is located in "VIP List" to "ServiceNow Prod" Incident table . Can you please show me how can this be done? Thank you.

VIP table - https://bessemertrustdev.service-now.com/api/now/v1/table/sys_user

LIB CONNECT TO 'VIP List (bgi-nt_deanb)';

Incidents table - https://bessemertrust.service-now.com/api/now/v1/table/incident

LIB CONNECT TO 'ServiceNow Prod (bgi-nt_deanb)';

So I loaded both in Qlick Load Editor

But not sure how to link the VIP table to Incident table as showing below. How can I combine them please help.

This is how it shows in Data Load Editor

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LIB CONNECT TO 'VIP List (bgi-nt_deanb)';

RestConnectorMasterTable:
SQL SELECT
"calendar_integration",
"country",
"last_login_time",
"u_corporate_title",
"source",
"sys_updated_on",
"building",
"web_service_access_only",
"notification",
"enable_multifactor_authn",
"sys_updated_by",
"sso_source",
"sys_created_on",
"state",
"vip",
"u_last_sync",
"sys_created_by",
"zip",
"home_phone",
"time_format",
"u_default_assignment_group",
"last_login",
"active",
"sys_domain_path",
"phone",
"u_start_date",
"name",
"employee_number",
"gender",
"city",
"failed_attempts",
"user_name",
"u_home_zip",
"title",
"sys_class_name",
"sys_id",
"u_last_4_of_home_phone",
"internal_integration_user",
"u_nickname",
"mobile_phone",
"street",
"company",
"first_name",
"email",
"introduction",
"preferred_language",
"sys_mod_count",
"last_name",
"photo",
"middle_name",
"sys_tags",
"time_zone",
"schedule",
"date_format",
"u_objectguid",
"u_service_provider",
"department",
"manager",
"__KEY_result",
(SELECT
"link",
"value",
"__FK_u_service_provider"
FROM "u_service_provider" FK "__FK_u_service_provider"),
(SELECT
"link" AS "link_u0",
"value" AS "value_u0",
"__FK_sys_domain"
FROM "sys_domain" FK "__FK_sys_domain"),
(SELECT
"link" AS "link_u1",
"value" AS "value_u1",
"__FK_department"
FROM "department" FK "__FK_department"),
(SELECT
"link" AS "link_u2",
"value" AS "value_u2",
"__FK_manager"
FROM "manager" FK "__FK_manager"),
(SELECT
"link" AS "link_u3",
"value" AS "value_u3",
"__FK_location"
FROM "location" FK "__FK_location")
FROM JSON (wrap off) "result" PK "__KEY_result";

[u_service_provider]:
LOAD [link] AS [link],
[value] AS [value],
[__FK_u_service_provider] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_u_service_provider]);

[sys_domain]:
LOAD [link_u0] AS [link_u0],
[value_u0] AS [value_u0],
[__FK_sys_domain] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_sys_domain]);

[department]:
LOAD [link_u1] AS [link_u1],
[value_u1] AS [value_u1],
[__FK_department] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_department]);

[manager]:
LOAD [link_u2] AS [link_u2],
[value_u2] AS [value_u2],
[__FK_manager] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_manager]);

[location]:
LOAD [link_u3] AS [link_u3],
[value_u3] AS [value_u3],
[__FK_location] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_location]);

[result]:
LOAD [calendar_integration] AS [calendar_integration],
[country] AS [country],
[last_login_time] AS [last_login_time],
[u_corporate_title] AS [u_corporate_title],
[source] AS [source],
[sys_updated_on] AS [sys_updated_on],
[building] AS [building],
[web_service_access_only] AS [web_service_access_only],
[notification] AS [notification],
[enable_multifactor_authn] AS [enable_multifactor_authn],
[sys_updated_by] AS [sys_updated_by],
[sso_source] AS [sso_source],
[sys_created_on] AS [sys_created_on],
[state] AS [state],
[vip] AS [vip],
[u_last_sync] AS [u_last_sync],
[sys_created_by] AS [sys_created_by],
[zip] AS [zip],
[home_phone] AS [home_phone],
[time_format] AS [time_format],
[u_default_assignment_group] AS [u_default_assignment_group],
[last_login] AS [last_login],
[active] AS [active],
[sys_domain_path] AS [sys_domain_path],
[phone] AS [phone],
[u_start_date] AS [u_start_date],
[name] AS [name],
[employee_number] AS [employee_number],
[gender] AS [gender],
[city] AS [city],
[failed_attempts] AS [failed_attempts],
[user_name] AS [user_name],
[u_home_zip] AS [u_home_zip],
[title] AS [title],
[sys_class_name] AS [sys_class_name],
[sys_id] AS [sys_id],
[u_last_4_of_home_phone] AS [u_last_4_of_home_phone],
[internal_integration_user] AS [internal_integration_user],
[u_nickname] AS [u_nickname],
[mobile_phone] AS [mobile_phone],
[street] AS [street],
[company] AS [company],
[first_name] AS [first_name],
[email] AS [email],
[introduction] AS [introduction],
[preferred_language] AS [preferred_language],
[sys_mod_count] AS [sys_mod_count],
[last_name] AS [last_name],
[photo] AS [photo],
[middle_name] AS [middle_name],
[sys_tags] AS [sys_tags],
[time_zone] AS [time_zone],
[schedule] AS [schedule],
[date_format] AS [date_format],
[u_objectguid] AS [u_objectguid],
[u_service_provider] AS [u_service_provider],
[department] AS [department],
[manager] AS [manager],
[__KEY_result] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_result]);

DROP TABLE RestConnectorMasterTable;

LIB CONNECT TO 'ServiceNow Prod (bgi-nt_deanb)';

RestConnectorMasterTable:
SQL SELECT
"parent",
"made_sla",
"u_schedule_date",
"caused_by",
"watch_list",
"upon_reject",
"sys_updated_on",
"child_incidents",
"hold_reason",
"approval_history",
"number",
"sys_updated_by",
"u_phone",
"user_input",
"sys_created_on",
"u_resolution_template",
"state",
"sys_created_by",
"knowledge",
"order",
"calendar_stc",
"u_ops_support",
"closed_at",
"cmdb_ci",
"impact",
"active",
"work_notes_list",
"business_service",
"priority",
"sys_domain_path",
"rfc",
"time_worked",
"expected_start",
"u_helpdesk_handledby_ops",
"opened_at",
"business_duration",
"group_list",
"work_end",
"reopened_time",
"resolved_at",
"approval_set",
"subcategory",
"work_notes",
"short_description",
"close_code",
"correlation_display",
"work_start",
"u_kb_article",
"additional_assignee_list",
"business_stc",
"description",
"calendar_duration",
"u_device_asset",
"close_notes",
"notify",
"sys_class_name",
"follow_up",
"parent_incident",
"sys_id",
"contact_type",
"reopened_by",
"incident_state",
"urgency",
"problem_id",
"company",
"reassignment_count",
"activity_due",
"severity",
"comments",
"u_incident_category",
"approval",
"sla_due",
"u_new_comment",
"comments_and_work_notes",
"due_date",
"sys_mod_count",
"reopen_count",
"sys_tags",
"u_incident_subcategory",
"escalation",
"upon_approval",
"correlation_id",
"category",
"__KEY_result",
(SELECT
"link",
"value",
"__FK_resolved_by"
FROM "resolved_by" FK "__FK_resolved_by"),
(SELECT
"link" AS "link_u0",
"value" AS "value_u0",
"__FK_opened_by"
FROM "opened_by" FK "__FK_opened_by"),
(SELECT
"link" AS "link_u1",
"value" AS "value_u1",
"__FK_sys_domain"
FROM "sys_domain" FK "__FK_sys_domain"),
(SELECT
"link" AS "link_u2",
"value" AS "value_u2",
"__FK_caller_id"
FROM "caller_id" FK "__FK_caller_id"),
(SELECT
"link" AS "link_u3",
"value" AS "value_u3",
"__FK_assignment_group"
FROM "assignment_group" FK "__FK_assignment_group"),
(SELECT
"link" AS "link_u4",
"value" AS "value_u4",
"__FK_closed_by"
FROM "closed_by" FK "__FK_closed_by"),
(SELECT
"link" AS "link_u5",
"value" AS "value_u5",
"__FK_assigned_to"
FROM "assigned_to" FK "__FK_assigned_to"),
(SELECT
"link" AS "link_u6",
"value" AS "value_u6",
"__FK_u_affected_user"
FROM "u_affected_user" FK "__FK_u_affected_user"),
(SELECT
"link" AS "link_u7",
"value" AS "value_u7",
"__FK_location"
FROM "location" FK "__FK_location"),
(SELECT
"link" AS "link_u8",
"value" AS "value_u8",
"__FK_cmdb_ci"
FROM "cmdb_ci" FK "__FK_cmdb_ci"),
(SELECT
"link" AS "link_u9",
"value" AS "value_u9",
"__FK_reopened_by"
FROM "reopened_by" FK "__FK_reopened_by")
FROM JSON (wrap off) "result" PK "__KEY_result";

[resolved_by]:
LOAD [link] AS [link],
[value] AS [value],
[__FK_resolved_by] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_resolved_by]);

[opened_by]:
LOAD [link_u0] AS [link_u0],
[value_u0] AS [value_u0],
[__FK_opened_by] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_opened_by]);

[sys_domain]:
LOAD [link_u1] AS [link_u1],
[value_u1] AS [value_u1],
[__FK_sys_domain] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_sys_domain]);

[caller_id]:
LOAD [link_u2] AS [link_u2],
[value_u2] AS [value_u2],
[__FK_caller_id] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_caller_id]);

[assignment_group]:
LOAD [link_u3] AS [link_u3],
[value_u3] AS [value_u3],
[__FK_assignment_group] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_assignment_group]);

[closed_by]:
LOAD [link_u4] AS [link_u4],
[value_u4] AS [value_u4],
[__FK_closed_by] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_closed_by]);

[assigned_to]:
LOAD [link_u5] AS [link_u5],
[value_u5] AS [value_u5],
[__FK_assigned_to] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_assigned_to]);

[u_affected_user]:
LOAD [link_u6] AS [link_u6],
[value_u6] AS [value_u6],
[__FK_u_affected_user] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_u_affected_user]);

[location]:
LOAD [link_u7] AS [link_u7],
[value_u7] AS [value_u7],
[__FK_location] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_location]);

[cmdb_ci]:
LOAD [link_u8] AS [link_u8],
[value_u8] AS [value_u8],
[__FK_cmdb_ci] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_cmdb_ci]);

[reopened_by]:
LOAD [link_u9] AS [link_u9],
[value_u9] AS [value_u9],
[__FK_reopened_by] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_reopened_by]);

[result]:
LOAD [parent] AS [parent],
[made_sla] AS [made_sla],
[u_schedule_date] AS [u_schedule_date],
[caused_by] AS [caused_by],
[watch_list] AS [watch_list],
[upon_reject] AS [upon_reject],
[sys_updated_on] AS [sys_updated_on],
[child_incidents] AS [child_incidents],
[hold_reason] AS [hold_reason],
[approval_history] AS [approval_history],
[number] AS [number],
[sys_updated_by] AS [sys_updated_by],
[u_phone] AS [u_phone],
[user_input] AS [user_input],
[sys_created_on] AS [sys_created_on],
[u_resolution_template] AS [u_resolution_template],
[state] AS [state],
[sys_created_by] AS [sys_created_by],
[knowledge] AS [knowledge],
[order] AS [order],
[calendar_stc] AS [calendar_stc],
[u_ops_support] AS [u_ops_support],
[closed_at] AS [closed_at],
[cmdb_ci] AS [cmdb_ci],
[impact] AS [impact],
[active] AS [active],
[work_notes_list] AS [work_notes_list],
[business_service] AS [business_service],
[priority] AS [priority],
[sys_domain_path] AS [sys_domain_path],
[rfc] AS [rfc],
[time_worked] AS [time_worked],
[expected_start] AS [expected_start],
[u_helpdesk_handledby_ops] AS [u_helpdesk_handledby_ops],
[opened_at] AS [opened_at],
[business_duration] AS [business_duration],
[group_list] AS [group_list],
[work_end] AS [work_end],
[reopened_time] AS [reopened_time],
[resolved_at] AS [resolved_at],
[approval_set] AS [approval_set],
[subcategory] AS [subcategory],
[work_notes] AS [work_notes],
[short_description] AS [short_description],
[close_code] AS [close_code],
[correlation_display] AS [correlation_display],
[work_start] AS [work_start],
[u_kb_article] AS [u_kb_article],
[additional_assignee_list] AS [additional_assignee_list],
[business_stc] AS [business_stc],
[description] AS [description],
[calendar_duration] AS [calendar_duration],
[u_device_asset] AS [u_device_asset],
[close_notes] AS [close_notes],
[notify] AS [notify],
[sys_class_name] AS [sys_class_name],
[follow_up] AS [follow_up],
[parent_incident] AS [parent_incident],
[sys_id] AS [sys_id],
[contact_type] AS [contact_type],
[reopened_by] AS [reopened_by],
[incident_state] AS [incident_state],
[urgency] AS [urgency],
[problem_id] AS [problem_id],
[company] AS [company],
[reassignment_count] AS [reassignment_count],
[activity_due] AS [activity_due],
[severity] AS [severity],
[comments] AS [comments],
[u_incident_category] AS [u_incident_category],
[approval] AS [approval],
[sla_due] AS [sla_due],
[u_new_comment] AS [u_new_comment],
[comments_and_work_notes] AS [comments_and_work_notes],
[due_date] AS [due_date],
[sys_mod_count] AS [sys_mod_count],
[reopen_count] AS [reopen_count],
[sys_tags] AS [sys_tags],
[u_incident_subcategory] AS [u_incident_subcategory],
[escalation] AS [escalation],
[upon_approval] AS [upon_approval],
[correlation_id] AS [correlation_id],
[category] AS [category],
[__KEY_result] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_result]);

DROP TABLE RestConnectorMasterTable;

topic How to link 2 tables in Qliksense pulled from ServiceNow in App Development

How to link 2 tables in Qliksense pulled from ServiceNow