CODEWITHSUNDEEP
concatenationqlikviewqliksense
alim1990
alim1990

Reputation: 4972

Qlik sense concatenate and join are merging tables horizontally instead of vertically

I have the following 2 tables:

[GP_BMI]:
LOAD
    gp_event_id as all_events_event_bmi,
    gp_dataElement as gp_height_bmi_de,
    gp_de_value as gp_height_bmi_value,
    'GP' as gp_height_bmi_stage
Resident GP
WHERE gp_dataElement='vOIPn23yOcr'
AND gp_status='COMPLETED';

LEFT JOIN (GP_BMI)
LOAD
    gp_event_id as all_events_event_bmi,
    gp_dataElement as gp_weight_bmi_de,
    gp_de_value as gp_weight_bmi_value
Resident GP
WHERE gp_dataElement='xNkRaZUG7fI'
AND gp_status='COMPLETED';

I am left joining here because I need both height and weight fields having IDs of vOIPn23yOcr and xNkRaZUG7fI respectively to be displayed horizontally in the table, because they come in a vertical way from the database source.

The second table is as follows:

[NUTRITION_BMI]:
LOAD
    nutrition_event_id as nutrition_all_events_event_bmi,
    nutrition_dataElement as nutrition_height_bmi_de,
    nutrition_de_value as nutrition_height_bmi_value,
    'Nutrition' as nutrition_height_bmi_stage
Resident Nutrition
WHERE nutrition_dataElement='vOIPn23yOcr'
AND nutrition_status='COMPLETED';

LEFT JOIN (NUTRITION_BMI)
LOAD
    nutrition_event_id as nutrition_all_events_event_bmi,
    nutrition_dataElement as nutrition_weight_bmi_de,
    nutrition_de_value as nutrition_weight_bmi_value
Resident Nutrition
WHERE nutrition_dataElement='xNkRaZUG7fI'
AND nutrition_status='COMPLETED';

Now I want to join these tables vertically in this case to display it in Qlik Sense table:

LOAD * Resident [PEDIATRIC_BMI];
JOIN(GP_BMI)
LOAD * Resident [NUTRITION_BMI];

But the result was horizontal display.

I tried:

LOAD * Resident [PEDIATRIC_BMI];
Concatenate(GP_BMI)
LOAD * Resident [NUTRITION_BMI];

And got the same table where all fields are displayed horizontally. But I need them vertically when combining tables as each table represent a stage.

Upvotes: 1

Views: 3364

Answers (1)

joel
joel

Reputation: 66

Problem

Table operations in Qlik are based on field names (which you can think of as column names).

When you perform a JOIN operation, the system looks for field/column names that are in common between the two tables, and tries to combine the rows of the tables based on shared values in those common fields.

This might create new rows if it is an OUTER JOIN (ie. the default join, which you have used here), but its purpose is to join the tables as you have said, horizontally.

The correct operation to do what you're intending - putting the rows on top of each other - is CONCATENATE.

The CONCATENATE operation adds new rows (vertically) onto some existing table. To do this it looks at the field/column names in the new rows. For any of them that match a field/column name in the existing table, the value will be put in that column. Otherwise, a new column will be automatically created to hold the value.

From looking at the above, I assume the intention is that each of the columns in the [NUTRITION_BMI] table should go below the columnsin the [GP_BMI] table with a similar name, ie:

  • nutrition_all_events_event_bmi under all_events_event_bmi
  • nutrition_height_bmi_de under gp_height_bmi_de

And so on. The issue is that since the fields have different names, per above Qlik will instead create a new column for each of them.

Solution

In order to concatenate the tables together, you need the field names to be the same. In order to distinguish between the different 'types' of rows, you need a field to indicate the 'type'. It looks like you're already doing this with the gp_height_bmi_stage field.

There are a few different orders you could do this in, one of them looks like this (note I have included the 'stage' field in the join, in case there is any overlap in the 'all_events_event_bmi' field between the different stages):

[BMI]:
LOAD
    gp_event_id as all_events_event_bmi,
    gp_dataElement as height_bmi_de,
    gp_de_value as height_bmi_value,
    'GP' as stage
Resident GP
WHERE gp_dataElement='vOIPn23yOcr'
AND gp_status='COMPLETED';
CONCATENATE
LOAD
    nutrition_event_id as all_events_event_bmi,
    nutrition_dataElement as height_bmi_de,
    nutrition_de_value as height_bmi_value,
    'Nutrition' as stage
Resident Nutrition
WHERE nutrition_dataElement='vOIPn23yOcr'
AND nutrition_status='COMPLETED';

[TMP_BMI_WEIGHTS]:
LOAD
    gp_event_id as all_events_event_bmi,
    gp_dataElement as weight_bmi_de,
    gp_de_value as weight_bmi_value,
    'GP' as stage
Resident GP
WHERE gp_dataElement='xNkRaZUG7fI'
AND gp_status='COMPLETED';
CONCATENATE
LOAD
    nutrition_event_id as all_events_event_bmi,
    nutrition_dataElement as weight_bmi_de,
    nutrition_de_value as weight_bmi_value,
    'Nutrition' as stage
Resident Nutrition
WHERE nutrition_dataElement='xNkRaZUG7fI'
AND nutrition_status='COMPLETED';

LEFT JOIN(BMI)
LOAD * RESIDENT TMP_BMI_WEIGHTS;
DROP TABLE TMP_BMI_WEIGHTS;

Upvotes: 1

Related Questions