SLA Development

[jaswanth222]

Hello Everyone, i have an urgent requirement. i'm creating an adhoc code in qliksense. i have 2 tables which is CSAT and Knowledge table in that table sys_created_on is the common field, i want to use that column for sla development. i have created an adhoc code but facing issue with the synthetic keys. please find the below script for reference. can anyone please help me on this.

NoConcatenate
Customer_Survey_Transformed:

Load
instance as Instance,
instance_task_id as Instance_task_id,
metric_question as Metric_Question,
actual_value as Actual_value,
string_value as String_Value,
sys_created_on as Created_On,
metric_metric_type as Metric_Type,
instance_task_id_assignment_group as Assignment_Group,
MonthName(Date(floor(sys_created_on))) as Created_On_Month,
If(Date(Floor(sys_created_on))>= Date#('$(vLastMonthStart)','YYYY-MM-DD') AND Date(FLOOR(sys_created_on)) <= Date#('$(vLastMonthEnd)','YYYY-MM-DD'), 1,0) as Created_On_Last_Month,
IF(Floor(sys_created_on)>=Floor(MonthStart(Today())) AND Floor(sys_created_on)<=Floor(MonthEnd(Today())),1,0) as Created_On_This_Month,
//IF(Floor(sys_created_on)>=Floor(MonthStart(Today())) and Floor(sys_created_on)<=Floor(Today()),1,0) as Created_On_This_Month,
IF(Floor(sys_created_on)>=Floor(monthstart(addmonths(Today(),-13))) and Floor(sys_created_on)<=Floor(Today()),1,0) as Created_On_13Month;

SELECT
instance,
`instance_task_id`,
`sys_created_on`,
`metric_question`,
`actual_value`,
`string_value`,
`metric_metric_type`,
`instance_task_id_assignment_group`
FROM `prod`.`dif_itsm_analytics_modelonly`.`asmt_metric_result`;

NoConcatenate
CSAT_metric_table_temp:
Load *
Resident Customer_Survey_Transformed;
drop table Customer_Survey_Transformed;
rename table CSAT_metric_table_temp to Customer_Survey_Transformed;

NoConcatenate
CSAT_metric_table_temp:
Load *,
if (not(Instance_task_id=''), Capitalize(Instance_task_id) & '-2246-5065',null()) as Key_Id
Resident Customer_Survey_Transformed
where wildmatch([String_Value], 'Very Satisfied','Satisfied','Dissatisfied','Very Dissatisfied');

drop table Customer_Survey_Transformed;
Rename table CSAT_metric_table_temp to Customer_Survey_Transformed;

NoConcatenate
KB_Knowledge_Table:
Load
sys_created_on as Created_On,
num(rating) as Rating,
number as Number,
workflow_state as workflow_state,
kb_knowledge_base as Kb_Knowledge_Base,
can_read_user_criteria as can_read_user_criteria,
IF(IsNull(can_read_user_criteria),1,0) as can_read_user_criteria_flag,
If(Not Isnull(num(rating)) and num(rating)>=3,1,0) as Rating_Flag;

SELECT
`sys_created_on`,
rating,
`number`,
`workflow_state`,
`kb_knowledge_base`,
`can_read_user_criteria`
From prod.dif_itsm_analytics_modelonly.kb_knowledge;

NoConcatenate
KB_Knowledge_Table_temp:
Load *
Resident KB_Knowledge_Table;
drop table KB_Knowledge_Table;
rename table KB_Knowledge_Table_temp to KB_Knowledge_Table;

NoConcatenate
KB_Knowledge_Table_temp:
Load *,
if (not(Number=''), Capitalize(Number) & '-2246-5065',null()) as Key_Id
Resident KB_Knowledge_Table;

drop table KB_Knowledge_Table;
Rename table KB_Knowledge_Table_temp to KB_Knowledge_Table;

[jaswanth222]

can anyone help on this please. its urgent requiremnt have to submit today.

[Mark_Little]

It is because the fields exist in both tables and a in Qlik you can only link on a single field.

Depending on your end goal you have a few options

1. Alise the field on one table to remove the link.
2. Create a new key which is a concatenate of the two fields
3. build a link table to sit in between

[jaswanth222]

i have tried to concatenate both tabls by using link table at the edge of the script then it is not getting any result