why does this CONCATENATE go wrong?

[amien]

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?

[#2]

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

[amien]

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;

[#4]

Do you can post your application or script file?

[amien]

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;

[#6]

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.