topic Re: Concatenate Question in QlikView

Concatenate Question

Wed, 26 Feb 2014 14:57:50 GMT

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

Re: Concatenate Question

alexandros17 — Wed, 26 Feb 2014 15:01:15 GMT

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

Re: Concatenate Question

MK_QSL — Wed, 26 Feb 2014 15:05:18 GMT

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;

Re: Concatenate Question

hic — Fri, 28 Feb 2014 16:03:43 GMT

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

Re: Concatenate Question

jaimeaguilar — Fri, 28 Feb 2014 17:36:34 GMT

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

Re: Concatenate Question

Tue, 25 Mar 2014 21:25:24 GMT

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