NoConcatenate CrossTables

[#1]

Hi, I'm having trouble with a script. I have two identical crosstables in Excel which I need to load into separate temp tables in my script. Usually I would use NoConcatenate to prevent them being added to the same table, but QV won't let me use that in conjunction with CrossTable. See my script below (including the NoConcatenate that fails)

TempBudgetUSA:
CrossTable(DocumentDate, Amount, 3)
LOAD Site,
 Include,
 [Account No],
 [13/12/2010],
 [20/12/2010],
 [27/12/2010],
 [03/01/2011]
FROM

(ooxml, embedded labels, table is Sheet1) where Include = 'y';
NoConcatenate
TempBudgetUK:
CrossTable(DocumentDate, Amount, 3)
LOAD Site,
 Include,
 [Account No],
 [13/12/2010],
 [20/12/2010],
 [27/12/2010],
 [03/01/2011]
FROM

(ooxml, embedded labels, table is Sheet1) where Include = 'y';

How can I use these keywords together - or do I need to rethink my script?

[Miguel_Angel_Baeyens · Accepted solution]

Hello Nick,

According to your code, I've modified some things

TempBudgetUSA:CrossTable(DocumentDateUSA, AmountUSA, 3)LOAD Site AS SiteUSA, Include AS IncludeUSA, [Account No] AS AccountNoUSA, [13/12/2010], [20/12/2010], [27/12/2010], [03/01/2011]FROM(ooxml, embedded labels, table is Sheet1) where Include = 'y'; TempBudgetUK:CrossTable(DocumentDateUK, AmountUK, 3)LOAD Site AS SiteUK, Include AS IncludeUK, [Account No] AS AccountNoUK, [13/12/2010], [20/12/2010], [27/12/2010], [03/01/2011]FROM(ooxml, embedded labels, table is Sheet1) where Include = 'y';

That should create two different tables with their fields suffixed by UK or USA.

Hope that helps

[#3]

Of course! Thanks that works perfectly!

[#4]

Is this workaround the only way to solve the problem? It's fine if you have something simple like the original post example with UK and USA which is meaningful and short to use as a suffix. It's not very helpful that QV defaults to concatenate if you can't override it!

[Anonymous]

What if the two tables are very big in size? I do not want to rename all of my fields