CODEWITHSUNDEEP
joinleft-joinqlikviewqliksense
Talcom
Talcom

Reputation: 11

Qlik Sense: Left Join two resident tables

I've got two tables:

violations_details:
LOAD
    "appname",
    "snapshot_id",
    "metric_id",
    "object_id",
    "object_type",
    "object_full_name",
    "metric_num_value",
    "status",
    "module_name",
    "Anz_Fehler",
    "violations_details_kz",
    "error_abs";
SQL SELECT 
    "appname",
    "snapshot_id",
    "metric_id",
    "object_id",
    "object_type",
    "object_full_name",
    "metric_num_value",
    "status",
    "module_name",
    1 as "Anz_Fehler",
    1 as "violations_details_kz",
    CASE WHEN status = 'Added' THEN 1 
         WHEN status = 'Deleted' THEN -1 
         ELSE 0
    END as error_abs
FROM "postgres"."cast_xapp_tools"."datapond_violations_details";

and

LOAD
    gen_id,
    gen_app,
    gen_patt1,
    gen_patt2,
    gen_class FROM [lib://AttachedFiles/gen_pattern.xlsx] (ooxml, embedded labels, table is tbl_gen_pattern);

Now I want to join column gen_id to table violation_details where object_full_name is like gen_patt1. If it does not match gen_id in violation_details have to be blank.

Size:

Any simple idea to do this? I think I have to use the resident clause within but every try failed.

Thanks

Upvotes: 0

Views: 7679

Answers (2)

Liam Hanninen
Liam Hanninen

Reputation: 1583

You are pretty close - actually you don't have to use resident since you can join it immediately when you load it. Qlik joins on fields with the same name. I'm not sure which field that you want so I just assumed that object_id was the same as gen_id (notice how I changed object_id to the alias of gen_id). So Qlik now knows to join the tables on that field (although you may want to join it on a different field).

violations_details:
LOAD
    "appname",
    "snapshot_id",
    "metric_id",
    "object_id" as gen_id,
    "object_type",
    "object_full_name",
    "metric_num_value",
    "status",
    "module_name",
    "Anz_Fehler",
    "violations_details_kz",
    "error_abs";
SQL SELECT 
    "appname",
    "snapshot_id",
    "metric_id",
    "object_id",
    "object_type",
    "object_full_name",
    "metric_num_value",
    "status",
    "module_name",
    1 as "Anz_Fehler",
    1 as "violations_details_kz",
    CASE WHEN status = 'Added' THEN 1 
         WHEN status = 'Deleted' THEN -1 
         ELSE 0
    END as error_abs
FROM "postgres"."cast_xapp_tools"."datapond_violations_details";

left join (violations_details)
LOAD
    gen_id,
    gen_app,
    gen_patt1,
    gen_patt2,
    gen_class 
FROM [lib://AttachedFiles/gen_pattern.xlsx] (ooxml, embedded labels, table is tbl_gen_pattern);

Upvotes: 0

EldadT
EldadT

Reputation: 932

Violations_details:
LOAD
    "appname",
    "snapshot_id",
    "metric_id",
    "object_id",
    "object_type",
    "object_full_name" as gen_id,
    "metric_num_value",
    "status",
    "module_name",
    "Anz_Fehler",
    "violations_details_kz",
    "error_abs";
SQL SELECT 
    "appname",
    "snapshot_id",
    "metric_id",
    "object_id",
    "object_type",
    "object_full_name",
    "metric_num_value",
    "status",
    "module_name",
    1 as "Anz_Fehler",
    1 as "violations_details_kz",
    CASE WHEN status = 'Added' THEN 1 
         WHEN status = 'Deleted' THEN -1 
         ELSE 0
    END as error_abs
FROM "postgres"."cast_xapp_tools"."datapond_violations_details";

Left join

LOAD
    gen_id,
    gen_app,
    gen_patt1,
    gen_patt2,
    gen_class FROM [lib://AttachedFiles/gen_pattern.xlsx]  (ooxml, embedded labels, table is tbl_gen_pattern);

Upvotes: 0

Related Questions