Want to replace value in a chart or table within heirarchy script

rsimson1 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a multi-fact table which records data at three different levels C = Course, CP = Course Period and SUB = Subject. I want to create charts which show all the SUB (Subjects) but has the Withdrawal Date at CP (Course Period) displayed against the SUB (Subjects) rather than the Withdrawal Date at SUB (Subject). The attached spreadsheet will help you understand what table looks like for one entry at Course, Course Period and Subject and what I'm trying to achieve. I'm not sure if this is best achieved in the script or in the chart/expression?

All help would be appreciated. Below is a bit of my script.... E_END is Withdrawal Date and E_TYPE is C,CP and SUB

EnrolmentsTemp:
Hierarchy (EnrolmentID, E_PARENT, E_REFERENCE)
LOAD
1 as EnrolmentIDCounter,
"E_ID" as [EnrolmentID],
"E_PARENT",
"E_REFERENCE",
SubField(E_REFERENCE, '/',+1) as Subject,

"E_MODE",
if("E_MODE" = '01', 'Fulltime', if("E_MODE" = '17', 'Fulltime',if("E_MODE" = '09', 'Evening', 'Part Time'))) as [Enrolment Mode],
date("E_END") as WithdrawDate,
month(E_END) as [Withdraw Month],
applymap('WithdrawMap', E_WITHDRAWAL, 'Missing') as [Withdraw Reason],
E_PASS,
applymap('SubjectResultsPassMap', Capitalize(Trim(E_PASS)), 'Missing') as [E_PASS_DESCRIPTION],
E_MODULE as ModuleID,
E_STUDENT & '-' & E_MODULE as [Funding Key],
E_MODULE & '-' & E_STUDENT as [Attendance Key],
applymap('WeightingMap',E_PROGRAMMEAREA, 1) as [Enrolment Sum Weighting],
E_STATUS,
"E_STUDENT" as [PersonID],
"E_TYPE",
"E_EMPLOYERPAYING",
//ApplyMap('OrganisationMap', "E_ENROLMENTEMPLOYER", 'Missing') as EmployerName,
date("E_START") as [Enrolment Date], // Applied date() function by RT on 04 May 2016
month(E_START) as [Enrolment Month],
"E_DESTINATION",
ApplyMap('EdestinationMap', E_DESTINATION,'Incomplete') as [Enrolment Destination],
ApplyMap('DestPosNegMap', E_DESTINATION,'Neutral') as [Enrolment Positive Destination],
Recno() as Attribute1
FROM 10_QVDFiles\RawData_Subject\ACEnrolments.qvd (QVD)

topic Want to replace value in a chart or table within heirarchy script in QlikView

Want to replace value in a chart or table within heirarchy script