Concatenate Question

[#1]

HI All,

I have two sets of data, one set has the following structure:

FACTS:

Load
Data,
PurgeChar(ForecastVersion,' ') AS [ForecastVersion],
Technology,
BusinessSplit,
Measure,
// Region & '|' & PenName as PenRegion,
Region,
PenName,
ShipDate,
Date(Date# (ShipDate , 'YYYYMM'),'MMM-YY') as Date

The other set has this structure:

FACTS2:

Load

Data,

PurgeChar(ForecastVersion,' ')  AS [ForecastVersion],

Measure,

//Region & '|' & PenName as PenRegion,
Region,

PenName,

ShipDate,

Date(Date# (ShipDate , 'YYYYMM'),'MMM-YY') as Date

As you can see, this generates a number of Synthetic keys. I tried resolving this a number ow ways, by creating a complex key, by renaming fields etc but I can't quite get the data set to work. It looks like concatenating the two tables would be a solution. Will the fact that I have two additional fields in the FACTS table (Technology and BusinessSplit) create issues? Can anyone help me in structuring the concatenation - I want to append the FACTS2 table to the end of the FACTS table.

Regards

John

[alexandros17 · Accepted solution]

It seems you will have a correct set of data simply concatening the two tables:

NewFacts:

noconcatenate:

Load * resident FACTS;

concatenate

load * resident FACTS2;

Drop tables FACTS:, FACTS2;

Hope it helps

[MK_QSL]

FACTS:

Load
Data & ‘|’ & PurgeChar(ForecastVersion,' ') & ‘|’ & Measure & ‘|’ & Region & PenName & ShipDate as Key,

Data,
PurgeChar(ForecastVersion,' ') AS [ForecastVersion],
Technology,
BusinessSplit,
Measure,
// Region & '|' & PenName as PenRegion,
Region,
PenName,
ShipDate,
Date(Date# (ShipDate , 'YYYYMM'),'MMM-YY') as Date

FROM….

FACTS2:

Load

Data & ‘|’ & PurgeChar(ForecastVersion,' ') & ‘|’ & Measure & ‘|’ & Region & PenName & ShipDate as Key,

Data,

PurgeChar(ForecastVersion,' ')  AS [ForecastVersion],

Measure,

//Region & '|' & PenName as PenRegion,
Region,

PenName,

ShipDate,

Date(Date# (ShipDate , 'YYYYMM'),'MMM-YY') as Date

FROM….

LinkTable:

Load Distinct

            Key,

            Key as Key2,

            Data,

ForecastVersion,

Measure,

Region,

PenName,

ShipDate

Resident FACTS

Concatenate

Load Distinct

            Key,

            Data,

ForecastVersion,

Measure,

Region,

PenName,

ShipDate

Resident FACTS2

Where NOT EXISTS (Key2, Key);

Drop Field Key2;

Drop Fields

            Key,

            Data,

ForecastVersion,

Measure,

Region,

PenName,

ShipDate

From FACTS2;

Drop Fields

            Key,

            Data,

ForecastVersion,

Measure,

Region,

PenName,

ShipDate

From FACTS;

[hic]

In my experience, a concatenation of several fact tables is always better than a link table. So I would do the same as what Alessandro suggests - but I would add some field that tells me which source this record comes from:

CombinedFacts:

Load ..., 'Facts1' as Source

     From FACTS;

Concatenate

Load ..., 'Facts2' as Source

     From FACTS2;

Further, you can even concatenate tables with different granularity. See http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

HIC

[jaimeaguilar]

Hi,

Just adding to what Henric Cronström suggested, you can use fields to identify every fact table and adding a HidePreffix in order to hide this field from appearing in current selections box and also can be treated as a system field so users won't be able to use these fields. So you can use that field for developing purposes:

Set HidePrefix: '_' // you can use whatever symbol you like

CombinedFacts:

Load ..., 'Facts1' as _Source

     From FACTS;

Concatenate

Load ..., 'Facts2' as _Source

     From FACTS2;

regards

[#6]

Hi Alessandro, All,

Apologies for not getting back to you sooner - it's very rude of me but I got swamped with other priorities. I worked out what I wasdoing wrong. My two tables are derived from Crosstables so (after a while and a bit more searching) I worked out how to get the Concatenate right. Thanks for the help

Regards

John