Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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