How to concatenate Crosstable ?

[amayuresh]

Hello,

I have below code with me.

SPEND:

CrossTable(Month, SPEND_Amount, 15)
LOAD [Plant Ledger],
[Supplier Number],
[Account Code],
[Supplier Commodity Code],
Spend,
[Nacis Coded],
[NAICS Number],
Category,
Commodity,
[Account Indicated],
[Ind Indicated],
Expr1,
[Morton's Number],
Ok,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
Indirect\Spend.xlsx
(ooxml, embedded labels, table is S_2012);

abcd:
CrossTable(Month, SPEND_Amount, 15)
LOAD [Plant Ledger],
[Supplier Number],
[Account Code],
[Supplier Commodity Code],
Spend,
[Nacis Coded],
[NAICS Number],
Category,
Commodity,
[Account Indicated],
[Ind Indicated],
Expr1,
[Morton's Number],
Ok,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
Indirect\Spend.xlsx
(ooxml, embedded labels, table is S_2013);

Concatenate

(SPEND)
LOAD * Resident abcd;

When I debug this code, it is giving error message as "Table Not FoundConcatenate staement.

So I have below questions,

1. What is the error in code ?

2. Seperate concatenation is not required, if two crosstables have same set of fields and in same sequence ?

Thank You,

Mayur

[MK_QSL]

Try

SPEND:

CrossTable(Month, SPEND_Amount, 15)
LOAD [Plant Ledger],
[Supplier Number],
[Account Code],
[Supplier Commodity Code],
Spend,
[Nacis Coded],
[NAICS Number],
Category,
Commodity,
[Account Indicated],
[Ind Indicated],
Expr1,
[Morton's Number],
Ok,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
Indirect\Spend.xlsx
(ooxml, embedded labels, table is S_2012);

Concatenate

CrossTable(Month, SPEND_Amount, 15)
LOAD [Plant Ledger],
[Supplier Number],
[Account Code],
[Supplier Commodity Code],
Spend,
[Nacis Coded],
[NAICS Number],
Category,
Commodity,
[Account Indicated],
[Ind Indicated],
Expr1,
[Morton's Number],
Ok,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
Indirect\Spend.xlsx
(ooxml, embedded labels, table is S_2013);

[alexandros17]

Unfortunately, when you use crosstable, you have to load the table at first then you must reload and concatenate to others so:

abcd:
CrossTable(Month, SPEND_Amount, 15)
LOAD .....

efgh:

noconcatenate

Load * resident abcd;

drop table abcd;

concatenate

.....

Hope it helps

[amayuresh]

This is giving error

"Illegal combination of prefixes

Concatenate".

[jonathandienst · Accepted solution]

Hi

Table abcd is never created because it has auto-concatenated into table SPEND. Just remove the

Concatenate (SPEND)

LOAD * Resident abcd;

statements.

HTH

Jonathan

[#6]

The second cross table also have the same table structure like first cross table. So these willl be automatically connected.

[rogerioqv]

Try:

SPEND:

CrossTable(Month, SPEND_Amount, 15)
LOAD [Plant Ledger],
[Supplier Number],
[Account Code],
[Supplier Commodity Code],
Spend,
[Nacis Coded],
[NAICS Number],
Category,
Commodity,
[Account Indicated],
[Ind Indicated],
Expr1,
[Morton's Number],
Ok,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
Indirect\Spend.xlsx
(ooxml, embedded labels, table is S_2012);

NOCONCATENATE

abcd:
CrossTable(Month, SPEND_Amount, 15)
LOAD [Plant Ledger],
[Supplier Number],
[Account Code],
[Supplier Commodity Code],
Spend,
[Nacis Coded],
[NAICS Number],
Category,
Commodity,
[Account Indicated],
[Ind Indicated],
Expr1,
[Morton's Number],
Ok,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
Indirect\Spend.xlsx
(ooxml, embedded labels, table is S_2013);

Concatenate

(SPEND)
LOAD * Resident abcd;

[amayuresh]

Thank you very much to all. Auto concatenate option works for me.