is this correct? why am I getting wrong output on charts for sum(salary) for each department?

[sharmaKavita]

//concatenating all date fields from each table 

TempCalendar:

LOAD

Distinct hire_date AS CalendarDate

RESIDENT employees;;

Concatenate(TempCalendar)

Load Distinct termination_date as CalendarDate

resident employees;;

Concatenate(TempCalendar)

LOAD

Distinct training_date AS CalendarDate

RESIDENT trainingAndDevelopment;

Concatenate(TempCalendar)

LOAD

Distinct promotion_date AS CalendarDate

RESIDENT promotions_final;

// Extract the Minimum and Maximum dates from a date column in the fact table. From the existing table

temp:

Load 

Min(CalendarDate) as mindate,

Max(CalendarDate) as maxdate

Resident TempCalendar;

DROP TABLE TempCalendar;

LET vmindate = num(Peek('mindate', 0, 'temp'));

LET vmaxdate = RangeMin(num(Peek('maxdate', 0, 'temp')),today());

Trace '$(vmindate)';

Trace '$(vmaxdate)' ;

//generates a sequence of dates starting from vmindate and ending at vmaxdate

Calendar:

LOAD

$(vmindate) + IterNo() -1 as Num,

Date($(vmindate) + IterNo() -1) as CalendarDate

AutoGenerate 1

While

($(vmindate) + IterNo() -1)<= $(vmaxdate);

MasterCalendar:

LOAD

CalendarDate,

Year(CalendarDate) AS Year,

Month(CalendarDate) AS Month,

Day(CalendarDate) AS Day,

Week(CalendarDate) AS Week,

Weekday(CalendarDate) AS Weekday,

QuarterEnd(CalendarDate) as quarterly,

MonthEnd(CalendarDate) as endofmonth

RESIDENT Calendar;

// let vend = Date(Peek('endofmonth',-1,'MasterCalendar'));

// Trace '$(vend)';

DROP TABLE Calendar;

Drop Table temp;

//link table will link master calendar to fact tables

LinkTable:

LOAD

hire_date as CalendarDate,

employee_id,

department_id,

hire_date&'|'&employee_id&'|'&department_id as %linkkey

RESIDENT employees;

Concatenate(LinkTable)

LOAD

training_date as CalendarDate,

employee_id,

training_date &'|'& employee_id as %linkkey

RESIDENT trainingAndDevelopment;

Concatenate(LinkTable)

LOAD

promotion_date as CalendarDate,

employee_id,

department_id,

promotion_date&'|'&employee_id&'|'&department_id as %linkkey

RESIDENT promotions_final;

drop Field employee_id from employees;

Drop Field employee_id From promotions_final;

drop Field employee_id From trainingAndDevelopment;

Drop Field department_id from employees;

Drop Field department_id from promotions_final;

NoConcatenate

Employees_Final:

Load *

Resident employees;

Drop table employees;

inner Join 

IntervalMatch(endofmonth)

Load 

hire_date,termination_date

Resident Employees_Final;

Drop Field endofmonth from MasterCalendar ;

[Chirantha]

The main operation where you may experience incorrect results could be related to your Concatenate(LinkTable) operations.Given that you're trying to create a LinkTable by joining multiple tables on different fields, if these fields have common data between them, it might create duplicate rows which may, in turn, affect your sum(salary) calculation. You could also face issues if the department_id or employee_id fields have any null values. The null values might break the link between the tables and result in inaccurate sums. A good way to debug this would be to check the tables and fields being loaded and their resulting output at each step of your script.

You can use the Table Viewer (Ctrl+T in the script editor) to inspect your data tables after each load step. Check for any anomalies such as null values or unexpected duplicates. Additionally, for the sum(salary) issue specifically, ensure you have the correct associations between your 'Employees_Final' table, your 'LinkTable', and your 'MasterCalendar'. The association of tables in Qlik is based on common field names, and it's crucial for accurate calculations.