topic Re: why does this CONCATENATE go wrong? in QlikView

why does this CONCATENATE go wrong?

amien — Fri, 08 Jul 2011 12:25:10 GMT

i really don't understand this.

i have two fact tables. some column names have the same name, some have not the same name.

When i do not use CONCATENATE all charts and values are fine (will create synthetic keys, but ok).

Now i use this :

CONCATENATE (common_cube)

LOAD *

RESIDENT orders;

DROP TABLE orders;

result will be for example that the column 'Value' of common_cube will be empty. 'Value' is allready loaded before the CONCATENATE. and orders table doesn't contain a field named 'Value'

How can this happen?

Re: why does this CONCATENATE go wrong?

Fri, 08 Jul 2011 12:41:53 GMT

Hi,

The Concatenate Will add rows in your new table. See this example:

TABLE_A:

ID	Value
A	1
B	2
C	3

TABLE_B:

ID	Info
D	TEST 1
E	TEST 2
F	TEST 3

When use CONCATENATE your new table are:

RESULT:

ID	Value	Info
A	1
B	2
C	3
D		TEST 1
E		TEST 2
F		TEST 3

When the fields of tables have same name, the values are input in same field.

When the values are different, the values are entered in different columns with null values

Re: why does this CONCATENATE go wrong?

amien — Fri, 08 Jul 2011 12:47:37 GMT

Thanks for your reply.

I understand what concatenate does, and your example is extactly what i want.

BUT when i do the concatenate, the result will be that the column 'Value' doesn't contain any values anymore .. all empty.

when i do not concatenate (and qv will add synthetic keys) 'Value' will not be empty and charts are fine.

only difference between them is this code:

CONCATENATE (common_cube)

LOAD *

RESIDENT orders;

DROP TABLE orders;

why does this CONCATENATE go wrong?

Fri, 08 Jul 2011 12:50:58 GMT

Do you can post your application or script file?

Re: why does this CONCATENATE go wrong?

amien — Tue, 12 Jul 2011 07:55:29 GMT

this is also strange:

table 1 contains about 300k rows and table 2 about 70k rows.

When i concatenate table 2 to table 1, the main table contains about 6 million records!

where i expect about 400k records

table1:

common_cube:

LOAD

FINANCE_GL_ACCOUNT_ID AS GL_Account_ID,

IF(Finance.GL_Account_Nr >= 300000 AND Finance.GL_Account_Nr <= 309999,1) AS GL_IndirectCosts,

Finance.GL_Account_Nr AS GL_Balance_Sheet,

FINANCE_VERSION_ID,

Finance.Version,

Finance.CustomerID AS COMMON.CustomerID,

Finance.Valuetype,

Finance.Valuetype.Group,

Finance.Amount * -1 AS Finance.Amount,

Finance.Amount.Unit,

Finance.Cumulative.Balance,

Finance.Cumulative.Balance.Unit,

FINANCE_CURRENCY_ID,

Finance.Currency,

FINANCE_COMPANY_ID,

Company.Name,

Finance.CurType,

Finance.CompCode,

NUM(TRIM(PERIOD_ID)) AS %calendar_key,

SUBFIELD(Fin.Date.FiscalPeriod,' ',2) AS Fin.Date.FiscalPeriod,

//Fin.Date.FiscalPeriod,

RIGHT(Fin.Date.FiscalPeriod,4) AS Fin.Date.FiscalYear,

PERIOD_ID,

Finance.HyperUnit AS COMMON.HyperUnit,

Finance.GL_Account_Nr,

//Finance.SubRegio,

SUBFIELD(Finance.SubRegio,' - ',2) AS COMMON.SubRegio,

ROWNO() AS Finance.rowno,

Finance.CostCentre,

Finance.CustomerName,

'MC01' AS COMMON.Label

RESIDENT financial2;

DROP TABLE financial2;

table2:

fc2011:

LOAD

TRIM(SUBFIELD(Country,'-',2)) AS FC2011.Country,

Data AS Finance.Amount,

Measure AS FC2011.MeasureOrg,

Region AS FC2011.Region,

Scenario AS FC2011.Scenario,

TRIM(SUBFIELD(Subregion,'-',3)) AS FC2011.SubregionOrg,

PURGECHAR(Period,'P') AS FC2011.Period,

PURGECHAR(Year,'Y') AS FC2011.Year,

PURGECHAR(Year,'Y') & PURGECHAR(Period,'P') AS %calendar_key,

'COMMERCIAL' AS COMMON.HyperUnitName,

'Forecast 1' AS Finance.Version

RESIDENT fc2011_tmp;

DROP TABLE fc2011_tmp;

concatenate:

CONCATENATE (common_cube)

LOAD

'TABLE2' AS COMMON.Label

RESIDENT fc2011;

DROP TABLE fc2011;

Re: why does this CONCATENATE go wrong?

Tue, 12 Jul 2011 12:40:24 GMT

Hi,

I think the problem your script is not part of this code.

You could diponibilizar your entire script using the option to export to file "Script Editor"> "File"> "Export to File ..."

In this way we could do a review of all parts of the script.

Only these two tables was not this error to occur.