Qlik Community

Qlik Support Discussions

Discussion Board for collaboration related to Qlik Support.

Highlighted
sweens78
Contributor

Include a field outside fact table

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;

1 Reply
parthesh
Contributor

Re: Include a field outside fact table

Hello,

Intervention ID will not be there in Fact table as it is coming from Interventions table, you have to join Fact and Interventions table and then you can load from that. and also i want to know what type of data will be there in Interventions table ? to check that is it possible to handle by writing some script logic.

 

Thanks,

Parthesh.

Community Browser