topic Re: Link 2 Facttables in App Development

Link 2 Facttables

Seier-Alsenz — Tue, 17 May 2022 13:30:58 GMT

Hello,

I am not coming up with the solution how to correctly connect a second facts table (payslip) with my previous master facts table. i am trying to stay in the Star-SChema. Namely, for the selection of tab 1 "employee core data", I would like to get the corresponding wages/salaries/hours or similar displayed. However, I can only link the two tables with one field.

If I link "MonthYear" I get the salary total for all companies.
If I link the PersonalID I get all salaries over all companies and each period as result.

To get the correct result from "Playlsip" I need about 5 selections from tab 1. It is only the linking of date, but also of wage type or also company.

The tables can not be JOINTed together, because the amount of data is too big.

Examples:

Main Tab 1: employee core data

ID, Name, Company, date of joining, type of employe, period of employment, monthyear, cost centre etc.

(How much workers i had MonthYear? How much were sick? Which type of employe i had when)

Tab 2: Payslip

ID, Name, Company Type of employe, motnyear, cost centre, wages, worked hours etc.

Do i need a unique ID that contains each selection? OR i have to Concatonate the Tables?

Re: Link 2 Facttables

lukas_ — Tue, 17 May 2022 14:14:16 GMT

Hello,

You can't link the two tables with the two keys? By creating a compound key

Re: Link 2 Facttables

vinieme12 — Tue, 17 May 2022 17:16:51 GMT

Create bridge table to a common calendar, as below

IF you need more clarity, please post some sample data

Employee:
Load
ID, Name, Company, dateofjoining, typeofemployee,periodofemployment,costcentre
,ID&'-'&Company as EmployeeKey
From employeedata;

Payslip:
Load ID, wages,workedhours, ID&'-'&Company&'-'&PayDate as PayslipKey
From payslipdata;

Bridge:
Load ID,EmployeeKey,dateofjoining as DATE,'JoiningDate' as DateType
Resident Employee;
Concatenate(Bridge)
Load ID,PayslipKey,PayDate as DATE,'PayDate' as DateType
Resident Payslip;

Drop field ID from Payslip;

MasterCalendar:
Load
TempDate AS DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('DATE', recno()))-1 as mindate,
max(FieldValue('DATE', recno())) as maxdate
AUTOGENERATE FieldValueCount('DATE');

exit Script;