Adding a join to a fact table without skewing the existing data result

[sweens78]

Hi Guys,

Is it possible to add some type of join from one table to a main fact table.  Without skewing the data on the main fact table.  When I do a join it my data is completely out.

I am trying to use a formula to query different table but when I go to reload it comes back saying it can find a particular outside the 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
Fact;

[Mark_Little]

Hi
Not hundred percent on what your are trying to do, when you are doing aggregated formula's you would need a group by statement. Something like

LOAD
OrderID,
SUM(VALUE) as NewValue
Resident OrderTable
Group By OrderID;

Mark

[sweens78]

Sorry Mark script included this time

thanks for reply mark below is my scipt I just want to add the intervention table to fact table.

set HidePrefix = '%';

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";

//Load Dimensions

Interventions:
LOAD "Interventions_ID",
    "Interventions_Types" AS [Interventions];
SQL SELECT "Interventions_ID",
    "Interventions_Types"
FROM "NCPMH_COPY_2".dbo."Dim_Interventions";

Basically I'm doing National Values against divisional values while using section Accews

So I'm using the below formula which is just one aspect of what the business wants  to do this.

But when I query it  says field isn't in fact table.  I'm new to Qlik so I'm just trying to figure out how I get the below formula to work when I reload and create the table.  Do I need to edit the formula in some way??

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

fact