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;
Yes, I understand the crosstab function / purpose, but I was meaning in respect of data structure, what would be the dimensions (Rows) ? Employee Name, Site, Department ?
Then the questions as the columnar data, answers as the attributes/data/body ?
Yes, dimensions would be information like names, department, period-fields and of course the extra Source field. How exactly the data-structure should be designed couldn't be answered in general because it depends on the kind of the surveys and the requirements to the reporting.
Especially if it's a bit more complex with dependencies between the questions (if for example the last answer triggers which new questions are shown) and/or there is a weighting/scoring of the answers is wanted. Nevertheless the more complex the matter is the more is benefit from the above concatenating + crosstable.
Very helpful is not to start with the complete data else just two concatenated facts with a few fields and limited to a dozen records within an empty app and just a few selection fields + one/two table-boxes and/or charts and then playing with the data and logic. And then step by step extending fields/records/facts a bit more.
