Include a field outside fact table

sweens78 — Tue, 04 Dec 2018 10:40:58 GMT

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 National value
Fact;

topic Include a field outside fact table in QlikView

Include a field outside fact table