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

sweens78 — Wed, 12 Dec 2018 10:42:02 GMT

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;

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

Mark_Little — Wed, 12 Dec 2018 11:13:08 GMT

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

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

sweens78 — Wed, 12 Dec 2018 12:24:13 GMT

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

topic Re: Adding a join to a fact table without skewing the existing data result in QlikView

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

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

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