Joining Two Resident Tables Created Using CrossTable

[#1]

Hi,

I have two tables I created via my load script using the crosstable statement for both. See below:

CrossQtr:
CROSSTABLE(Measure, QtrValue,5)
LOAD *
RESIDENT TempQtr;
CrossYr:
CROSSTABLE(Measure, YrValue,4)
LOAD *
RESIDENT TempYr;
[/CODE]
Both tables end up having several fields that are identical, except for the last field being QtrValue & YrValue. What I want to do is JOIN both tables into a single table. I used the following code but it ends up not doing anything:
<pre>
Data:
JOIN(CrossQtr) LOAD * RESIDENT CrossYr;
DROP TABLES CrossQtr, CrossYr;

Can anyone tell me what i'm doing wrong?

[johnw]

Use CONCATENATE instead of JOIN.

[#3]

Still doesn't work. For some reason it all just gets dropped and I end up with nothing, no tables, no data, nothing...

[johnw]

Can you post an example using inline loads?

[Anonymous]

The "Data" table was never created - instead it was CrossQtr, the one you dropped in the next line.

[#6]

Ok here's the code using INLINE loads:

 TempQtr:
 LOAD * INLINE [
 Quarter, FiscalYear, TotalRev, EBT, Opex
 Q1, 2009, 1000, 200, 300
 Q2, 2009, 3123, 323, 232
 Q3, 2009, 2323, 323, 323
 Q4, 2009, 2322, 323, 332
];

TempYr:
 LOAD * INLINE [
 FiscalYear, TotalRev, EBT, Opex
 2009, 9878, 45645, 4567
 2009, 52562, 3253, 2532
 2009, 254656, 3243, 3235
 2009, 232522, 3253, 3325];

CrossQtr:
 CROSSTABLE(Measure, QtrValue,2)
 LOAD * RESIDENT TempQtr;

CrossYr:
 CROSSTABLE(Measure, YrValue,1)
 LOAD * RESIDENT TempYr;

Finances:
 LOAD * RESIDENT CrossQtr;
 JOIN LOAD * RESIDENT CrossYr;

DROP TABLES TempQtr, TempYr, CrossQtr, CrossYr;

[johnw · Accepted solution]

Michael's right about why your data disappeared. I didn't catch that at first. As for fixing the problem, you need NOCONCATENATE before the first load to Finances, and CONCATENATE instead of JOIN for the second:

Finances:
NOCONCATENATE LOAD * RESIDENT CrossQtr;
CONCATENATE LOAD * RESIDENT CrossYr;

The first tells it to make a NEW table instead of just giving CrossQtr an alias of Finances. The second tells it to add the rows from CrossYr even though the fields don't all match the existing Finances table.

[Anonymous]

Same thing... See this part:
Finances:
LOAD * RESIDENT CrossQtr;
Obviously the table Finance was not created because it contains the same exactly fields as CrossQtr. The simplies way to fix is to use only
JOIN (CrossQtr) LOAD * RESIDENT CrossYr;
And, do not drop CrossQtr, because tjhis is the one you want to keep.

[Anonymous]

Forgot NONCONCATENATE - yes, techically it is simpler to add just one key word.

[johnw]

Michael Solomovich wrote:Forgot NONCONCATENATE - yes, techically it is simpler to add just one key word.

*chuckle* Yeah, but nicer for maintenance if we get rid of some temp tables. I got it down to one temp table like this:

Finances:
CROSSTABLE(Measure, QtrValue,2)
LOAD * INLINE [
Quarter, FiscalYear, TotalRev, EBT, Opex
Q1, 2009, 1000, 200, 300
Q2, 2009, 3123, 323, 232
Q3, 2009, 2323, 323, 323
Q4, 2009, 2322, 323, 332
];
Temp:
CROSSTABLE(Measure, YrValue,1)
LOAD * INLINE [
FiscalYear, TotalRev, EBT, Opex
2009, 9878, 45645, 4567
2009, 52562, 3253, 2532
2009, 254656, 3243, 3235
2009, 232522, 3253, 3325
];
CONCATENATE ([Finances])
LOAD * RESIDENT Temp;
DROP TABLE Temp;

I would have EXPECTED the following to work without any temp tables, but it did not:

Finances:
CROSSTABLE(Measure, QtrValue,2)
LOAD * INLINE [
Quarter, FiscalYear, TotalRev, EBT, Opex
Q1, 2009, 1000, 200, 300
Q2, 2009, 3123, 323, 232
Q3, 2009, 2323, 323, 323
Q4, 2009, 2322, 323, 332
];
CONCATENATE LOAD *;
CROSSTABLE(Measure, YrValue,1)
LOAD * INLINE [
FiscalYear, TotalRev, EBT, Opex
2009, 9878, 45645, 4567
2009, 52562, 3253, 2532
2009, 254656, 3243, 3235
2009, 232522, 3253, 3325
];

Might be a way, though.