Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Amberj_29
Contributor III
Contributor III

Creating a Key to link two tables by 2 columns

Hello,

I am new-ish to QLik sense, in that I have never touch the script before. I have manged to create a key to link two tables with two columns - i.e. when I view the data model viewer it looks right, also when I select a dimension of metric, the three fields are there - table1_col, table2_col, and merged col. However, where I am having the issue is when I try to do visualisation with the merged col. 

I want a P&L table for my budget, actuals data. Both are in two separate sheets, and are based by month. So I have merged both month and product group (as that is what they have in common), please see below for the script. As stated previously, I can make metrics using table1_month and table2_month, but when I go to use the merged Key_month it does not work. 

IS this able to be done in a P&L table? Or I may have made an error in the script. either way please help 🙂

Script in order of table 1 then table 2:

LOAD
"Month" as "Month1",
"Product ID",
"Product - ISBN",
"Product Name",
"Commissioned by",
"Royalty %",
"Stock Levels",
Medium,
Category,
Language,
"Publication Date",
"Volume per period",
"Price (RRP in £",
"Total Gross Value",
"Total Discounts",
"%",
"Net Receipts",
Royalty,
Classification,
"Product Type",
"Category Group",
"Description_Group" as "Description_Group1",
autonumber(Month&'-'&Description_Group) as Key,
'Sales Data' as Type
FROM [lib://mydata.xlsx]
(ooxml, embedded labels, table is [Sales Data]);

LOAD
"Budget Type",
Category_Number,
"Description_Group" as "Description_Group2",
"Month" as "Month2",
autonumber(Month&'-'&Description_Group) as Key,
'Budget_Forecast' as Type,
"Budget Figures",
"Forecast figures"
FROM [lib://mydata2.xlsx]
(ooxml, embedded labels, table is Budget_Forecast);

Common_temp:

load

"Month1" as Month,

"Description_Group1" as Description_Group,

Type as Common_Type

resident [Sales Data];

concatenate

Load

"Month2" as Month,

"Description_Group2" as Description_Group,

Type as Common_Type

resident [Budget_Forecast];

Common:

LOAD *,

autonumber(Month&'-'&Description_Group) as Key

Resident Common_temp;

drop table Common_temp;

drop field Type;

------------------------------------

I found this help via this board for the script:  https://community.qlik.com/t5/New-to-QlikView/Associate-table-based-on-more-than-1-column/td-p/12225... 

0 Replies