Concatenate Two Crosstables

[#1]

Hi!

Thanks for clicking on this question. I have this script:

Parts:

CrossTable([Last Name], [Parts AOP Data])

LOAD [FiscalMonthYear] as [Fiscal Month Year],

     LastNameC,

     LastNameRo,

     LastNameA,

     LastNameG,

     LastNameW,

     LastNameG,

     LastNameP,

     LastNameRe,

     LastNameH,

     LastNameT

    // Sum

    // M,

     //N,

     //O,

     //P,

    // Q

FROM

(ooxml, embedded labels, table is [By name]);

PHH:

CrossTable([FiscalMonthYear], PHHData, 1)

LOAD //[Client #],

    // [Unit #],

    // [Current Breakdown],

     Capitalize([Last Name])as [Last Name],

     //[Employee ID],

    // [Unique ID],

    // [First Name],

   //  VIN,

    // [Charge Category],

     [41640] as [JAN-14],

     [41671] as [FEB-14],

     [41702] as [MAR-14],

     [41733] as [APR-14],

     [41764] as [MAY-14],

     [41795] as [JUN-14],

     [41826] as [JUL-14],

     [41857] as [AUG-14],

     [41888] as [SEP-14],

     [41919] as [OCT-14],

     [41950] as [NOV-14],

     [41981] as [DEC-14]

    // YTD

FROM

(ooxml, embedded labels, header is 4 lines, table is PHH);

How do I concatenate these two tables? Ideally, I will have four filled-out columns:

1) FiscalMonthYear

2) Last Name

3) Parts AOP Data

4) PHH Data

This is the only way to stop looping and synthetic tables in my database. You can't just concatenate with the concatenate function because it'll say illegal combination of prefixes.Thanks for all your help!

[krishna_2644 · Accepted solution]

How about CrossTable 'ing all your tables individually first with no concatenates,

Then concatenating the resultant tables from resident into your target table name.

Then dropping the temporary CrossTable 'd tables.

How to join two crosstab tables?

SCRIPT LOOKS LIKE THIS :

Parts_TEMP:

CrossTable([Last Name], [Parts AOP Data])

LOAD [FiscalMonthYear] as [Fiscal Month Year],

     LastNameC,

     LastNameRo,

     LastNameA,

     LastNameG,

     LastNameW,

     LastNameG,

     LastNameP,

     LastNameRe,

     LastNameH,

     LastNameT

    // Sum

    // M,

     //N,

     //O,

     //P,

    // Q

FROM

(ooxml, embedded labels, table is [By name]);

PHH_TEMP:

CrossTable([FiscalMonthYear], PHHData, 1)

LOAD //[Client #],

    // [Unit #],

    // [Current Breakdown],

     Capitalize([Last Name])as [Last Name],

     //[Employee ID],

    // [Unique ID],

    // [First Name],

   //  VIN,

    // [Charge Category],

     [41640] as [JAN-14],

     [41671] as [FEB-14],

     [41702] as [MAR-14],

     [41733] as [APR-14],

     [41764] as [MAY-14],

     [41795] as [JUN-14],

     [41826] as [JUL-14],

     [41857] as [AUG-14],

     [41888] as [SEP-14],

     [41919] as [OCT-14],

     [41950] as [NOV-14],

     [41981] as [DEC-14]

    // YTD

FROM

(ooxml, embedded labels, header is 4 lines, table is PHH);

NoConcatenate

END_TABLE:

LOAD *

RESIDENT  Parts_TEMP;

Concatenate(END_TABLE)

load *
Resident PHH_TEMP;

drop tableS Parts_TEMP,PHH_TEMP;

LET ME KNOW ONCE YOU ARE DONE.

tHANKS

kRISHNA