Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
acepeda71
Contributor II
Contributor II

Join two Cross Tables through a unique field

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.

Capture.PNG

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

Capture2.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

3 Replies
sunny_talwar

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;

View solution in original post

acepeda71
Contributor II
Contributor II
Author

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