Create Link Table for my sales versus my budget

[mcoetzee]

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

[Anil_Babu_Samineni]

Try to share full script with link table

I may suggest, If script is long use attachment instead of pasting on the wall

[mcoetzee]

//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;

[Brett_Bleess]

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