Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
it finally works!.. using Inner Join
Thank you very much!
That is great, please close the thread by marking correct and helpful responses.
Best,
Sunny