Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
mcoetzee
New Contributor

Create Link Table for my sales versus my budget

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

 

 

3 Replies
Highlighted

Re: Create Link Table for my sales versus my budget

Try to share full script with link table

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
mcoetzee
New Contributor

Re: Create Link Table for my sales versus my budget

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

Highlighted
Digital Support
Digital Support

Re: Create Link Table for my sales versus my budget

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.