Multiple unlinked Fact tables & universal filter

Oggy172 · ‎2024-12-30

Hi all,

I am building a data model for our HR department, and we have various surveys that I pull the data for.

Exit Interviews
1 Month Onboarding
3 Month Onboarding
6 Month Onboarding

There's no requirement to have these data sources linked, each one will have it's own sheet - however I want a filter to be universal for all sheets. e.g Site, Department etc.

How is it best to achieve this?? I am relatively new to Qlik, so unsure what options are available to me.

I have the 4 tables as below, and then a calendar to link it to our fiscal calendar.

Exit:
LOAD
    Exit.Id,
    "Exit.Completion time",
    date("Exit.Completion time",'dd/MM/yyyy') as "Exit.Completion Date",
    "Exit.Enter Your Name",
    ApplyMap('MapSiteTypos',Exit.Location) as Exit.Site,
    "Exit.Please confirm your exit date",
    "Exit.Which department do you work in?",
    "Exit.Please confirm your job title"
......
;

Onboarding1:
LOAD
    OnBoarding1.Id,
    "OnBoarding1.Start time",
    "OnBoarding1.Completion time",
    floor("OnBoarding1.Completion time") as "OnBoarding1.Completion Date",
    OnBoarding1.Email,
    "OnBoarding1.Please confirm your name",
    "OnBoarding1.Please confirm your start date",
    ApplyMap('MapSiteTypos',"OnBoarding1.Please select which site you are based at (If you work from more than one site, please state the site you work at mainly)") as Onboarding1.Site,
    "OnBoarding1.Please confirm which department you work in" as Onboarding1.Department,
    Capitalize("OnBoarding1.If you are assigned to a shift, please confirm which shift") as Onboarding1.Shift,
    "OnBoarding1.If you are assigned to a line, please confirm which line" as Onboarding1.Line,
    "OnBoarding1.Job Title"

.....
;

Onboarding6:
LOAD
    OnBoarding6.Id,
    "OnBoarding6.Start time",
    "OnBoarding6.Completion time",
    date("OnBoarding6.Completion time",'dd/MM/yyyy') as "OnBoarding6.Completion Date",
    OnBoarding6.Email,
    "OnBoarding6.Please confirm your name",
    "OnBoarding6.Please confirm your start date",
    ApplyMap('MapSiteTypos',"OnBoarding6.Site (if you are based from more than one site, please state your main site)") as Onboarding6.Site,
    "OnBoarding6.Please confirm which department you work in" as Onboarding6.Department,
    Capitalize("OnBoarding6.If you are assigned to a shift, please confirm which shift") as Onboarding6.Shift,
    "OnBoarding6.If you are assigned to a line, please confirm which line" as Onboarding6.Line,
    "OnBoarding6.Please confirm your job title"

......
;

Calendar:

Calendar:
	LOAD 
	 "Date"   as LINK_Date, 
     FiscalWeek, 
     FiscalPeriod, 
     FiscalYear, 
     CalendarWeek, 
     CalendarPeriod, 
     CalendarYear
	 FROM [lib://QVD Data:Office_365_Sharepoint - a@b.com/Shared Documents/QlikSaaS/Calendar2.qvd]
	 (qvd)
	WHERE "Date" >= 45109;

Clement15 · ‎2024-12-30

Hello,
You must have a table with common fields to do what you want. Here is an example that I made with your code. I used an Inline that does not have access to your data

Exit:

LOAD

    Exit.Id,

    "Exit.Completion time",

    Date("Exit.Completion time",'dd/MM/yyyy') as "Exit.Completion Date",

    "Exit.Enter Your Name",

    Exit.Site,

    "Exit.Please confirm your exit date",

    "Exit.Which department do you work in?" as Exit.Departement,

    "Exit.Please confirm your job title"

INLINE [

Exit.Id, Exit.Completion time, Exit.Enter Your Name, Exit.Site, Exit.Please confirm your exit date, Exit.Which department do you work in?, Exit.Please confirm your job title

1, 2023-12-30 12:30:00, John Doe, Site A, 2023-12-20, IT, Manager

2, 2023-12-29 14:00:00, Jane Smith, Site B, 2023-12-18, HR, Analyst

3, 2023-12-28 16:45:00, Emily Davis, Site C, 2023-12-15, Finance, Coordinator

4, 2023-12-27 09:30:00, John Doe, Site A, 2023-12-10, IT, Manager

5, 2023-12-26 11:15:00, Jane Smith, Site B, 2023-12-05, HR, Analyst

6, 2023-12-25 13:00:00, Emily Davis, Site C, 2023-12-03, Finance, Coordinator

7, 2023-12-24 15:30:00, John Doe, Site A, 2023-12-01, IT, Manager

8, 2023-12-23 10:45:00, Jane Smith, Site B, 2023-11-30, HR, Analyst

9, 2023-12-22 14:15:00, Emily Davis, Site C, 2023-11-28, Finance, Coordinator

10, 2023-12-21 08:30:00, John Doe, Site A, 2023-11-25, IT, Manager

];




Onboarding1:

LOAD

    OnBoarding1.Id,

    "OnBoarding1.Start time",

    "OnBoarding1.Completion time",

    Floor("OnBoarding1.Completion time") as "OnBoarding1.Completion Date",

    OnBoarding1.Email,

    "OnBoarding1.Please confirm your name",

    "OnBoarding1.Please confirm your start date",

    OnBoarding1.Site,

    OnBoarding1.Department,

    OnBoarding1.Shift,

    OnBoarding1.Line,

    "OnBoarding1.Job Title"

INLINE [

OnBoarding1.Id, OnBoarding1.Start time, OnBoarding1.Completion time, OnBoarding1.Email, OnBoarding1.Please confirm your name, OnBoarding1.Please confirm your start date, OnBoarding1.Site, OnBoarding1.Department, OnBoarding1.Shift, OnBoarding1.Line, OnBoarding1.Job Title

1, 2023-11-20 08:00:00, 2023-12-01 12:00:00, jdoe@company.com, John Doe, 2023-11-15, Site A, IT, Morning, Line 1, Manager

2, 2023-11-20 09:00:00, 2023-12-02 13:00:00, jsmith@company.com, Jane Smith, 2023-11-10, Site B, HR, Day, Line 1, Analyst

3, 2023-11-20 10:00:00, 2023-12-03 14:00:00, edavis@company.com, Emily Davis, 2023-11-05, Site C, Finance, Evening, Line 1, Coordinator

4, 2023-11-20 11:00:00, 2023-12-04 15:00:00, jdoe@company.com, John Doe, 2023-11-01, Site A, IT, Night, Line 1, Manager

5, 2023-11-20 12:00:00, 2023-12-05 16:00:00, jsmith@company.com, Jane Smith, 2023-10-28, Site B, HR, Morning, Line 1, Analyst

6, 2023-11-20 13:00:00, 2023-12-06 17:00:00, edavis@company.com, Emily Davis, 2023-10-25, Site C, Finance, Day, Line 1, Coordinator

7, 2023-11-21 14:00:00, 2023-12-07 18:00:00, jdoe@company.com, John Doe, 2023-10-20, Site A, IT, Evening, Line 1, Manager

8, 2023-11-21 15:00:00, 2023-12-08 19:00:00, jsmith@company.com, Jane Smith, 2023-10-15, Site B, HR, Night, Line 1, Analyst

9, 2023-11-21 16:00:00, 2023-12-09 20:00:00, edavis@company.com, Emily Davis, 2023-10-10, Site C, Finance, Morning, Line 1, Coordinator

10, 2023-11-21 17:00:00, 2023-12-10 21:00:00, jdoe@company.com, John Doe, 2023-10-05, Site A, IT, Day, Line 1, Manager

];




Onboarding6:

LOAD

    OnBoarding6.Id,

    "OnBoarding6.Start time",

    "OnBoarding6.Completion time",

    Date("OnBoarding6.Completion time",'dd/MM/yyyy') as "OnBoarding6.Completion Date",

    OnBoarding6.Email,

    "OnBoarding6.Please confirm your name",

    "OnBoarding6.Please confirm your start date",

    OnBoarding6.Site,

    OnBoarding6.Department ,

    OnBoarding6.Shift,

    OnBoarding6.Line,

    "OnBoarding6.Please confirm your job title"

INLINE [

OnBoarding6.Id, OnBoarding6.Start time, OnBoarding6.Completion time, OnBoarding6.Email, OnBoarding6.Please confirm your name, OnBoarding6.Please confirm your start date, OnBoarding6.Site, OnBoarding6.Department, OnBoarding6.Shift, OnBoarding6.Line, OnBoarding6.Please confirm your job title

1, 2023-11-20 08:30:00, 2023-11-20 12:30:00, adavis@company.com, Alice Davis, 2023-11-01, Site X, IT, Morning, Line 1, Supervisor

2, 2023-11-21 09:30:00, 2023-11-21 13:30:00, bsmith@company.com, Bob Smith, 2023-10-28, Site Y, HR, Day, Line 1, Engineer

3, 2023-11-22 10:30:00, 2023-11-22 14:30:00, cjohnson@company.com, Charlie Johnson, 2023-10-25, Site Z, Finance, Evening, Line 1, Technician

4, 2023-11-23 11:30:00, 2023-11-23 15:30:00, adavis@company.com, Alice Davis, 2023-10-20, Site X, IT, Night, Line 1, Supervisor

5, 2023-11-24 12:30:00, 2023-11-24 16:30:00, bsmith@company.com, Bob Smith, 2023-10-15, Site Y, HR, Morning, Line 1, Engineer

6, 2023-11-25 13:30:00, 2023-11-25 17:30:00, cjohnson@company.com, Charlie Johnson, 2023-10-10, Site Z, Finance, Day, Line 1, Technician

7, 2023-11-26 14:30:00, 2023-11-26 18:30:00, adavis@company.com, Alice Davis, 2023-10-05, Site X, IT, Evening, Line 1, Supervisor

8, 2023-11-27 15:30:00, 2023-11-27 19:30:00, bsmith@company.com, Bob Smith, 2023-10-01, Site Y, HR, Night, Line 1, Engineer

9, 2023-11-28 16:30:00, 2023-11-28 20:30:00, cjohnson@company.com, Charlie Johnson, 2023-09-28, Site Z, Finance, Morning, Line 1, Technician

10, 2023-11-29 17:30:00, 2023-11-29 21:30:00, adavis@company.com, Alice Davis, 2023-09-25, Site X, IT, Day, Line 1, Supervisor

];










Calendar:

LOAD 

     "Date"   as LINK_Date, 

     FiscalWeek, 

     FiscalPeriod, 

     FiscalYear, 

     CalendarWeek, 

     CalendarPeriod, 

     CalendarYear

INLINE [

Date, FiscalWeek, FiscalPeriod, FiscalYear, CalendarWeek, CalendarPeriod, CalendarYear

2023-11-20, 1, Q1, 2023, 51, Dec, 2023

2023-11-19, 2, Q1, 2023, 51, Dec, 2023

2023-11-18, 3, Q1, 2023, 51, Dec, 2023

2023-11-17, 4, Q1, 2023, 51, Dec, 2023

2023-11-16, 5, Q1, 2023, 51, Dec, 2023

2023-11-15, 6, Q1, 2023, 50, Dec, 2023

2023-11-14, 7, Q1, 2023, 50, Dec, 2023

2023-11-13, 8, Q1, 2023, 50, Dec, 2023

2023-11-12, 9, Q1, 2023, 50, Dec, 2023

2023-11-11, 10, Q1, 2023, 50, Dec, 2023

];







Link_Table:

Load

 Exit.Id,

    Exit.Site as Site,

    "Exit.Completion Date" as LINK_Date,

    Exit.Departement as Departement

Resident Exit;




Concatenate




Load

 OnBoarding1.Id,

    OnBoarding1.Site as Site,

    OnBoarding1.Department as Departement,

    OnBoarding1.Shift as Shift,

    OnBoarding1.Line  as Line ,

    "OnBoarding1.Completion Date" as LINK_Date

Resident Onboarding1;




Concatenate




Load

 OnBoarding6.Id,

    OnBoarding6.Site as Site,

    OnBoarding6.Department as Departement,

    OnBoarding6.Shift as Shift,

    OnBoarding6.Line  as Line ,

    "OnBoarding6.Completion Date" as LINK_Date

Resident Onboarding6;

marcus_sommer · ‎2025-01-02

The simple magic of (nearly) all data-models is to merge all facts into a single fact-table. It's much simpler as keeping all facts separate and will save a lot of efforts - just by concatenating them together by harmonizing the field-names and data-structures as much as possible.

Your surveys contain in general the same data and to be able to differentiate between them an extra field with the source is added - which could be then used as selection/dimension/set expression within the UI.

Maybe with something like this:

t: load ID, Name, Start, ..., 'Month 1' as Source from Month1Survey;
concatenate(t)
load ID, Name, Start, ..., 'Month 3' as Source from Month3Survey;

There is further no need that all facts have the exact identically questions because everything which isn't there is just NULL which is nowhere a problem.

Further sensible may be to harmonize the data-structure more by applying a crosstable-transforming to all the questions and answers fields.

Oggy172 · ‎2025-01-02

Hi @marcus_sommer

That's what I ended up doing, a concatenation of the onboarding surveys, qualified

Onboarding1.Question1
Onboarding1.Question2
Onboarding1.Question3
Onboarding1.Site as [Site]
Onboarding3.Question1
Onboarding3.Question2
Onboarding3.Question3
Onboarding3.Site as [Site]
Onboarding6.Question1
Onboarding6.Question2
Onboarding6.Question3
Onboarding6.Site as [Site]

but I'm also going to look into @Clement15 solution as I want to broaden my knowledge with this application

Thank you both.

marcus_sommer · ‎2025-01-02

The fields shouldn't be loaded qualified - it are the same data and the differentiation would be applied with the field [Source].

Oggy172 · ‎2025-01-02

The data is already qualified - it's taken from our Time Management Software in QV and exported to .qvd on sharepoint, where we take it from for QS. It is qualified during this process (legacy, pre-dating me)

It's qualified at this point.

marcus_sommer · ‎2025-01-02

IMO it's a bad design-decision in beforehand which should be changed - maybe not instantly else in the mid-term by developing the next business-release. The relevant source-information is better applied within the file-name and/or as an extra column.

In your case you could rename the fields again to remove the field-prefix - directly within the table-load or in your case also per ALIAS-statements before the load.

Oggy172 · ‎2025-01-02

It's something I'll look to tidy up, but a lot of the questions are not the same - it might appear they are from the snapshot of data, but the bulk of the questions deviate in each survey after the initial 'demographic' ones.

Appreciate your help.

marcus_sommer · ‎2025-01-02

Like mentioned above there is no logically and/or technically problem by creating unsynchronized data-structures from more or less different facts. But usually should be the majority of fields the same. If the differences between the facts are bigger or if the number of fields are rather large it would be useful to transform the questions + answers per crosstable into two fields.

A further benefit of such an approach would be to be able to categorize the questions into n main/sub-groups, for example in regard to the salary, leader, team, ...

Oggy172 · ‎2025-01-02

Hi @marcus_sommer

I'm not sure what you mean by building them into cross-tabs.

I understand what a crosstab is, but struggling to visualise what you are suggesting.

Data Prep