Need to merge two data sets, having more than a column common

[Sagar082]

Hi everyone,

Good morning. I would like to concatenate two data tables; which has dimensions like Zone, Region and Area in common and count.

I created new field by concatenation of column zone, region and area and try to merge the data sets using that column name in common in both dataset. But, is not producing desire result. Instead it is allocating of all values of second data set under dimension "-" implies null.

I insist of concatenation of column as there will be repeation of values due to repeation in zone, region and area.

Can someone suggest the solution.

[Dalton_Ruer]

You can CONCATENATE multiple fields together to form a CONCATENATED KEY.  

This post has a lot of discussion with different examples depending on whether you want to use a separator character or not.  https://community.qlik.com/t5/QlikView-App-Dev/Concatenate-two-fields/td-p/1151946

The concatenate operator is the & character

[rafael_capote_qlik]

Hi,

If you want to concatenate the tables you can simply rename the common fields on both tables with a common name and concatenate the tables on the scirpt like this:

FACT_TABLE:
LOAD
  Zone1 as Zone,
  Area1 as Area,
  Region1 as Region,
  Sum(Measure1) as Measure1
FROM Source1.qvd (qvd)
GROUP BY
  Zone1, Area1, Region1;

Concatenate(FACT_TABLE)
LOAD
  Zone2 as Zone,
  Area2 as Area,
  Region2 as Region,
  Sum(Measure2) as Measure2
FROM Source2.qvd (qvd)
GROUP BY
  Zone2, Area2, Region2;

If you want to keep them linked but in different tables on the data model then you can do something like the following logic:

FACT_TABLE:
LOAD
Zone1&'_'&Area1&'_'&Region1 as Key,
Sum(Measure1) as Measure1
FROM Source1.qvd (qvd)
GROUP BY
Zone1, Area1, Region1;

Concatenate(FACT_TABLE)
LOAD
Zone2&'_'&Area2&'_'&Region2 as Key,
Sum(Measure2) as Measure2
FROM Source2.qvd (qvd)
GROUP BY
Zone2, Area2, Region2;

M_Dimension:
LOAD DISTINCT
Key,
Subfield(Key,'_',1) as Zone,
Subfield(Key,'_',2) as Area,
Subfield(Key,'_',3) as Region
RESIDENT FACT_TABLE;

Hope this helps.