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...
