Hi Guys
I have the below script Im trying to do a Divisional v National KPI board.
I'm trying to query the intervention table below is there anyway to join this to the fact table with out it altering the calculation already in the fact table,
I've placed a join on the the intervention and it works but it throws my values out then on the fact table
Fact:
LOAD ConcatID,
ConcatID AS NEWKEYID,
"Date_Formatted" AS Date,
DateKey,
EthnicityID AS EthnicBackground_ID,
GenderID,
GUID,
HospitalID,
HospitalID AS NEWHOSPID,
"NOK_Carer_ID",
PaID,
"PatientAssessedby_ID",
"PatientsDischargedFromED_ID",
"PNS_ID" AS PatientsNotAssessed_ID,
"PRTEDBY_ID",
SubstanceMisuseAlsoAFactor,
"TimeBSPAx_ID",
"TimeNextApptAllOthers_ID",
"TimeNextCMHTAppt_ID",
"TimeNextGPAppt_ID",
"TimeNextHSEAddictionAppt_ID",
"TimePresented_ID",
UID,
"YearAge_ID";
SQL SELECT ConcatID,
"Date_Formatted",
DateKey,
EthnicityID,
GenderID,
GUID,
HospitalID,
"NOK_Carer_ID",
PaID,
"PatientAssessedby_ID",
"PatientsDischargedFromED_ID",
"PNS_ID",
"PRTEDBY_ID",
SubstanceMisuseAlsoAFactor,
"TimeBSPAx_ID",
"TimeNextApptAllOthers_ID",
"TimeNextCMHTAppt_ID",
"TimeNextGPAppt_ID",
"TimeNextHSEAddictionAppt_ID",
"TimePresented_ID",
UID,
"YearAge_ID"
FROM "NCPMH_COPY_2".dbo."FACT_SingleValueMetrics";
inner keep(Fact) //Keep table structure seperate
Calendar:
LOAD CalendarQuarter,
'Q'&''&CalendarQuarter AS CalendarQuarter2,
CalendarYear,
CalendarYearMonth,
CalendarYearQtr,
DateKey,
DateName,
DayNameOfWeek,
Left(DayNameOfWeek, 3) AS DayNameOfWeekShort,
DayOfMonth,
DayOfWeek,
DayOfYear,
FiscalMonthOfYear,
FiscalQuarter,
FiscalYear,
FiscalYearMonth,
FiscalYearQtr,
FullDate,
IsLastDayOfMonth,
MonthName,
Left(MonthName, 3) AS MonthNameShort,
MonthOfYear,
WeekdayWeekend,
WeekOfYear;
SQL SELECT CalendarQuarter,
CalendarYear,
CalendarYear AS Year,
CalendarYearMonth,
CalendarYearQtr,
DateKey,
DateName,
DayNameOfWeek,
DayOfMonth,
DayOfWeek,
DayOfYear,
FiscalMonthOfYear,
FiscalQuarter,
FiscalYear,
FiscalYearMonth,
FiscalYearQtr,
FullDate,
IsLastDayOfMonth,
MonthName,
MonthOfYear,
WeekdayWeekend,
WeekOfYear
FROM "NCPMH_COPY_2".dbo."Dim_Calander";
Interventions: //Want to join this to the fact table for below calculation to work
LOAD "Interventions_ID",
"Interventions_Types" AS [Interventions];
SQL SELECT "Interventions_ID",
"Interventions_Types"
FROM "NCPMH_COPY_2".dbo."Dim_Interventions";
---------------------------------------------------------------------------------------------------------------------------------------
//Calculation below does not see Intervention ID as it is not in Fact Table
LOAD
Num((count(if (Interventions_ID = '1' and TimeBSPAx_ID = '1', NEWKEYID))-Count(if (Interventions_ID = '1' and TimeBSPAx_ID = '1', PatientsNotAssessed_ID)))/(Count(NEWKEYID)-Count(PatientsNotAssessed_ID)),'##.##%') AS AGGR
Resident //Formula for Nation al value
Fact;
