Join Cross Tables

[#1]

Hi,

Is there a way to join two or more cross tables? I tried but got an error.

//This is what I am doing in the script:

Data:
load * Inline [
Name, SP, AR1, BR1, CR1, AR2, BR2, CR2
X, 101, 22675, 1025432, 4856952, -, 69524, 1111426
];

OutputData1:
crosstable(Sales, Numbers,2)
load Name, SP, AR1, BR1, CR1 resident Data;

join(OutputData1)
OutputData2:
crosstable(Margin, MarginNumbers,2)
load Name, SP, AR2, BR2, CR2 resident Data;

I am getting the error:

Illegal combination of prefixes

join(OutputData1)

OutputData2:

crosstable(Margin, MarginNumbers,2)

load Name, SalesPerson, FTD2, MTD2, YTD2 resident Data

[AbhijitBansode]

First complete all the crosstables you want and then join them using Resident

[alexandros17]

It is not possible to have join directly on cross tables, you have to

1) create crosstables

2) reload them and perform the join

TMP:

noconcatenate

Load * resident OutputData1;

join

Load * resident OutputData2;

drop tables OutputData1, OutputData2;

let me know

[#4]

Hello...Thanks for your quick input, it solved the issue partially. I was looking for join in order to produce the out put like : (desired output)

Name	SP	Sales	Numbers	Margin	MarginNumbers
X	101	AR1	22675	AR2	-
X	101	BR1	1025432	BR2	69524
X	101	CR1	4856952	CR2	1111426

But when I applied join it created 9 records (3 x 3 for resident table Output1 and Output2). The result that I received from JOIN is: (actual output from JOIN)

Name	SP	Sales	Numbers	Margin	MarginNumbers
X	101	AR1	22675	AR2	-
X	101	BR1	1025432	AR2	-
X	101	CR1	4856952	AR2	-
X	101	AR1	22675	BR2	69524
X	101	BR1	1025432	BR2	69524
X	101	CR1	4856952	BR2	69524
X	101	AR1	22675	CR2	1111426
X	101	BR1	1025432	CR2	1111426
X	101	CR1	4856952	CR2	1111426

[alexandros17]

I do not know your data structure, maybe you need left join and not join.

If not this, please sent me your tables in order to fix

[#6]

Well, my input data is of the following kind (where the first line is header):

Input Data:

Name, SP, AR1, BR1, CR1, AR2, BR2, CR2                    //Header

X, 101, 22675, 1025432, 4856952, -, 69524, 1111426     //This is record

Y, 102, 34571, 1019231, 5712813, -, 24738, 1111697     //This is record

and I need the output in the following format:

Desired Output Data:

Name	SP	Sales	Numbers	Margin	MarginNumbers
X	101	AR1	22675	AR2	-
X	101	BR1	1025432	BR2	69524
X	101	CR1	4856952	CR2	1111426
Y	102	AR1	34571	AR2	-
Y	102	BR1	1019231	BR2	24738
Y	102	CR1	5712813	CR2	1111697

Please note that the first two columns Name and SP are the same as input data whereas next column Sales (in output result) is header columns (AR1, BR1, CR1) and column Numbers consists the values for AR1, BR1 and CR1. Similarly, Margin (in output result) contains AR2, BR2 and CR2 and MarginNumbers contains the values.

That's why I thought of using Crosstable concept to devide the data in two temporary tables. After Crosstable operation, one table conatins Name, SP, Sales and Numbers (3 rows for Name=X and SP=101) and another table contains Name, SP, Margin and MarginNumbers (3 rows for Name=x and SP=101).

TemporaryTable1:

Name	SP	Sales	Numbers
X	101	AR1	22675
X	101	BR1	1025432
X	101	CR1	4856952

TemporaryTable2:

Name	SP	Margin	MarginNumbers
X	101	AR2	-
X	101	BR2	69524
X	101	CR2	1111426

But a JOIN operation results in a Cartesian Product and results in 9 rows.

Actual output after join:

Name	SP	Sales	Numbers	Margin	MarginNumbers
X	101	AR1	22675	AR2	-
X	101	BR1	1025432	AR2	-
X	101	CR1	4856952	AR2	-
X	101	AR1	22675	BR2	69524
X	101	BR1	1025432	BR2	69524
X	101	CR1	4856952	BR2	69524
X	101	AR1	22675	CR2	1111426
X	101	BR1	1025432	CR2	1111426
X	101	CR1	4856952	CR2	1111426

While, I need:

Name	SP	Sales	Numbers	Margin	MarginNumbers
X	101	AR1	22675	AR2	-
X	101	BR1	1025432	BR2	69524
X	101	CR1	4856952	CR2	1111426

Sorry for putting so much information!! Please help.

Thanks.