Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am trying to create a link table but does not seem to get it right.
I have Sales Classifications in my budgets per fiscal year per month per branch but don't have any sales that was done and need to create a link table to show all values regardless of where there was a sale.
Example:
My dimension is my sales classification (for my actual) and not my sales classification in my budget.
When looking at the below table i have clients that was budget but there was no sales done.
SalesClassification | PY | CY | Budget |
RESIDENTIAL | 50 | 52 | 100 |
SMALL COMMERCIAL | 20 | 18 | 50 |
NKA | 100 | 122 | 200 |
GUARDING | 20 | 15 | 50 |
- | 0 | 0 | 100 |
Try to share full script with link table
I may suggest, If script is long use attachment instead of pasting on the wall
//Budget Import from Excel File
Budget:
LOAD
FiscalYear & Num((FiscalMonth),'00')& BusArea & SubClassGroup As %KeyFiscalBusArea,
FiscalYear & Num((FiscalMonth),'00')& BusArea as LinkKey,
Description As BG_Des,
SwitchDesc As BG_Swi,
SubClassGroup As Budget_SubClass,
Budget As BG_Bud
FROM
[Subscriber Movements FY20 - Budgets.xlsm]
(ooxml, embedded labels, table is [Budget Import]);
//Actuals direct from DB
Sales:
LOAD
MasterFiscalYear & Num(MasterFiscalMonth,'00') & ClientBusArea & SubClassGroupClassification As LinkKey,
SubClassGroupClassification As Actual_SubClass
Resident ClientLogTransActions;
//Create Link table to show all Sales Classifications regardless of where a sale was made
LinkTable:
LOAD
FiscalYear & Num(FiscalMonth,'00')& BusArea & SubClassGroup as LinkKey,
Description As BG_Description,
SwitchDesc As BG_SwitchDesc,
SubClassGroup As BG_SubClassGroup, // Budget Sales Classification
Budget As BG_Budget // Budget per Sales | Attrition Classification
From [Subscriber Movements FY20 - Budgets.xlsm]
(ooxml, embedded labels, table is [Budget Import];
Concatenate
LOAD
MasterFiscalYear & Num(MasterFiscalMonth,'00') & ClientBusArea & SubClassGroupClassification As LinkKey,
SubClassGroupClassification As AC_SubClass //Actuals Sales Classification
Resident ClientLogTransActions;
Drop Fields FiscalYear,FiscalMonth;
Best I have to help further is the following Design Blog post, hopefully it may clarify something for you to where you can get it sorted. My post will also kick things back up, so someone else my holler as well...
https://community.qlik.com/t5/Qlik-Design-Blog/Concatenate-vs-Link-Table/ba-p/1467569
Regards,
Brett