topic Join two Cross Tables through a unique field in QlikView

Join two Cross Tables through a unique field

Anonymous — Sun, 07 Jan 2018 23:30:52 GMT

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

Re: Join two Cross Tables through a unique field

sunny_talwar — Mon, 08 Jan 2018 12:40:51 GMT

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;

Re: Join two Cross Tables through a unique field

Anonymous — Tue, 09 Jan 2018 00:04:58 GMT

it finally works!.. using Inner Join

Thank you very much!

Re: Join two Cross Tables through a unique field

sunny_talwar — Tue, 09 Jan 2018 01:16:08 GMT

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

Best,

Sunny