0 Replies Latest reply: Jun 29, 2017 1:41 PM by Thomas Ødegaard RSS

    Missing link between ParentChild and Calendar

    Thomas Ødegaard

      Hi,

       

      I am having trouble with my parent child hierarachy at it does not react as I expecting when I am choosing a month.

       

      For example if I choose Year Month = 201703, it should only show me the amount in my fact table for records within my parent child hierarchy, that are valid in that month.

       

      So in the example attached it should only show me the record where LevelName2 = CD, however the fields DW_ValidFrom_Organisation and DW_ValidTo_Organisation does not show the right figures.

       

      My script in Qlik Sense is:

       

      Calendar:

      LOAD "DW_EK_Date" as "DW_SK_Date",

        "Year",

          "Year_StartDate",

          "Year_EndDate",

          Quarter,

          "Quarter_Key",

          "Month_Key" as "YearMonthNum",

          "Month_Char" as Month,

          "Month_Name_EN",

          "Month_StartDate",

          "Month_EndDate",

          Left("DW_EK_Date",6) as YearMonth

      FROM "excel file"

      (ooxml, embedded labels, table is Calendar);

       

      Organisation:

      Hierarchy(DW_EK_Organisation, DW_EK_OrganisationParent,LevelName)

      LOAD

          DW_SK_Organisation,

          DW_EK_Organisation,

          LoadDate_Organisation,

          LevelID,

          LevelName,

          LevelType,

          DW_EK_OrganisationParent,

          OrganisationLevel,

          Date(DW_ValidFrom_Organisation,'YYYY-MM-DD hh:mm:ss'),

          Date(DW_ValidTo_Organisation,'YYYY-MM-DD hh:mm:ss'),

          NewestOrganisation,

          MaxLoadDate_Organisation,

          DeletedOrganisation

      FROM "excel file"

      (ooxml, embedded labels, table is Org);

       

       

      Fact:

      LOAD

          DW_SK_Organisation,

          DW_SK_Account,

          DW_SK_IncomeStatementsFTE,

          DW_SK_Date,

          LoadDate_IncomeStatements,

          TransactionDate,

          "Type",

          Amount,

          AdjustmentComment,

          MaxLoadDate_IncomeStatements

      FROM "excel file"

      (ooxml, embedded labels, table is Fct);

       

      Best regards,

      Thomas