Join two Cross Tables through a unique field

[Anonymous]

Hello,

I'm trying to join two cross tables that belongs the same original Table source.
The objective is to convert monthly Units (month_UNI) columns and monthly sales (month_CR) columns into rows, and re-join data in one table using a unique "Month" column.

Unfortunately, the result is as shown.  Sales and Units records doesn't appear in the same row as in the original table.

This is the script I'm currently using:

ODBC CONNECT TO BSALE;
CrossTable(Month,Units,4)
Units:
LOAD Status,Periodo,"Nombre_Producto","Stock_Number","Ene_UNI" as "Ene","Feb_UNI" as "Feb","Mar_UNI" as "Mar","Abr_UNI" as "Abr","May_UNI" as "May",
"Jun_UNI" as "Jun","Jul_UNI" as "Jul","Ago_UNI" as "Ago","Sep_UNI" as "Sep","Oct_UNI" as "Oct","Nov_UNI" as "Nov", "Dic_UNI" as "Dic";
SQL SELECT Status,Periodo,"Nombre_Producto","Stock_Number","Ene_UNI","Feb_UNI","Mar_UNI","Abr_UNI", "May_UNI","Jun_UNI","Jul_UNI","Ago_UNI","Sep_UNI","Oct_UNI","Nov_UNI", "Dic_UNI"
FROM "BSALE".dbo."BA-TEST";

CrossTable(Month,Sales,4)
Sales:
LOAD Status,Periodo,"Nombre_Producto","Stock_Number","Ene_CR" as "Ene","Feb_CR" as "Feb","Mar_CR" as "Mar","Abr_CR" as "Abr","May_CR" as "May",
"Jun_CR" as "Jun","Jul_CR" as "Jul","Ago_CR" as "Ago","Sep_CR" as "Sep","Oct_CR" as "Oct","Nov_CR" as "Nov", "Dic_CR" as "Dic";
SELECT Status,Periodo,"Nombre_Producto","Stock_Number","Ene_CR","Feb_CR","Mar_CR","Abr_CR", "May_CR","Jun_CR","Jul_CR","Ago_CR","Sep_CR","Oct_CR","Nov_CR", "Dic_CR"
FROM "BSALE".dbo."BA-Test";

NoConcatenate
Result1:
Load *
Resident Units;
Concatenate(Result1)

Load *
Resident Sales;

Drop Tables Units,Sales

Thanks in advance for your help.

Regards,

Alex Cepeda

[sunny_talwar · Accepted solution]

I think instead of concatenate, you need a join... try like this

ODBC CONNECT TO BSALE;

CrossTable(Month,Units,4)

Result1:

LOAD Status,

Periodo,

"Nombre_Producto",

"Stock_Number",

"Ene_UNI" as "Ene",

"Feb_UNI" as "Feb",

"Mar_UNI" as "Mar",

"Abr_UNI" as "Abr",

"May_UNI" as "May",

"Jun_UNI" as "Jun",

"Jul_UNI" as "Jul",

"Ago_UNI" as "Ago",

"Sep_UNI" as "Sep",

"Oct_UNI" as "Oct",

"Nov_UNI" as "Nov",

"Dic_UNI" as "Dic";

SQL SELECT Status,Periodo,"Nombre_Producto","Stock_Number","Ene_UNI","Feb_UNI","Mar_UNI","Abr_UNI", "May_UNI","Jun_UNI","Jul_UNI","Ago_UNI","Sep_UNI","Oct_UNI","Nov_UNI", "Dic_UNI"

FROM "BSALE".dbo."BA-TEST";

CrossTable(Month,Sales,4)

Join (Result1)

LOAD Status,Periodo,

"Nombre_Producto",

"Stock_Number",

"Ene_CR" as "Ene",

"Feb_CR" as "Feb",

"Mar_CR" as "Mar",

"Abr_CR" as "Abr",

"May_CR" as "May",

"Jun_CR" as "Jun",

"Jul_CR" as "Jul",

"Ago_CR" as "Ago",

"Sep_CR" as "Sep",

"Oct_CR" as "Oct",

"Nov_CR" as "Nov",

"Dic_CR" as "Dic";

SELECT Status,Periodo,"Nombre_Producto","Stock_Number","Ene_CR","Feb_CR","Mar_CR","Abr_CR", "May_CR","Jun_CR","Jul_CR","Ago_CR","Sep_CR","Oct_CR","Nov_CR", "Dic_CR"

FROM "BSALE".dbo."BA-Test";

or this

ODBC CONNECT TO BSALE;

CrossTable(Month,Units,4)

Units:

LOAD Status,

Periodo,

"Nombre_Producto",

"Stock_Number",

"Ene_UNI" as "Ene",

"Feb_UNI" as "Feb",

"Mar_UNI" as "Mar",

"Abr_UNI" as "Abr",

"May_UNI" as "May",

"Jun_UNI" as "Jun",

"Jul_UNI" as "Jul",

"Ago_UNI" as "Ago",

"Sep_UNI" as "Sep",

"Oct_UNI" as "Oct",

"Nov_UNI" as "Nov",

"Dic_UNI" as "Dic";

SQL SELECT Status,Periodo,"Nombre_Producto","Stock_Number","Ene_UNI","Feb_UNI","Mar_UNI","Abr_UNI", "May_UNI","Jun_UNI","Jul_UNI","Ago_UNI","Sep_UNI","Oct_UNI","Nov_UNI", "Dic_UNI"

FROM "BSALE".dbo."BA-TEST";

CrossTable(Month,Sales,4)

Sales:

LOAD Status,Periodo,

"Nombre_Producto",

"Stock_Number",

"Ene_CR" as "Ene",

"Feb_CR" as "Feb",

"Mar_CR" as "Mar",

"Abr_CR" as "Abr",

"May_CR" as "May",

"Jun_CR" as "Jun",

"Jul_CR" as "Jul",

"Ago_CR" as "Ago",

"Sep_CR" as "Sep",

"Oct_CR" as "Oct",

"Nov_CR" as "Nov",

"Dic_CR" as "Dic";

SELECT Status,Periodo,"Nombre_Producto","Stock_Number","Ene_CR","Feb_CR","Mar_CR","Abr_CR", "May_CR","Jun_CR","Jul_CR","Ago_CR","Sep_CR","Oct_CR","Nov_CR", "Dic_CR"

FROM "BSALE".dbo."BA-Test";

NoConcatenate

Result1:

Load *

Resident Units;

Join (Result1)

Load *

Resident Sales;

Drop Tables Units,Sales;

[Anonymous]

it finally works!.. using Inner Join

Thank you very much!

[sunny_talwar]

That is great, please close the thread by marking correct and helpful responses.

Best,

Sunny