No link between tables

[Anonymous]

Hi all,

I have a dataset with 3 tables: Calendar, Organisation (as a parent child hiearachy), and a fact table.

"DW_SK_Date" (format=YYYYMMDD) is a primary key between calendar and fact table.

"DW_SK_Organisation" is a primary key between the fact table and organisation hierarchy.

The fact table only consists of the most aggregated members of the organisation hierarchy.

All members in the organisation hierarchy have two fields "DW_ValidFrom_Organisation" and "DW_ValidTo_Organisation" (format=YYYYMMDD) indicating in what period the member is valid.

My issue is, that my calendar only connect to the lowest aggregated member in the Organisation hierarchy as it connects to the fact table. So when I select a period (a month for instance) in the calendar, it does not responds to changes that has been made for members that are less aggregated in the organisation hierarchy.

How can I make a connection between the calendar table and the Organisation hierarchy dimension?

In the attached example it should catch all members that I have marked with yellow, when I choose YearMonth = 201703 as these members are valid in that March 2017

My script 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 "My 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,'YYYYMMDD') as DW_ValidFrom_Organisation,

    Date(DW_ValidTo_Organisation,'YYYYMMDD') as DW_ValidTo_Organisation,

    NewestOrganisation,

    MaxLoadDate_Organisation,

    DeletedOrganisation

FROM "My 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 "My excel file"

(ooxml, embedded labels, table is Fct);

Please share your thoughts

- Thomas

[rittermd]

Qlik associates tables by matching the field names.  If they are identical then they associate.

So in your load just rename the field to match the other table.

[Anonymous]

I have tried that, but then it gives me synthetic key and circular reference.

Also, please notice that my organisation hierarchy consists of a valid from and to date, so my needs to know if I select a month between these two dates it shall see the member as valid.

I have tried with IntervalMatch and a "While AddMonths(DW_ValidFrom_Organisation,IterNo()-1) <= DW_ValidTo_Organisation" to generate rows with dates between the validFrom and ValidTo dates.

[jmvilaplanap]

You have to use the Intervalmatch function but you must to remove the synthetic and circular references

Synthetic keys  . How to remove them

Removing Circular Reference

[rittermd]

Then you need to get creative.  You can create a new key by concatenating multiple fields and then the tables can associate with this key if you need multiple fields.  If you don't need multiple fields then make sure that the other fields do not have the same name by changing one of them.

[Anonymous]

Still haven't been able to solve it.

Can anyone try to take the challenge on my attached dataset?

[balabhaskarqlik]

You can create a different field in Master calendar for each table. So for example you have three tables 1,2,3 and they

each have Date1,Date2, Date3 then in master calendar you could create aliases like Date as Date1, Date as Date2 ,

Date as Date3.

Rename all date fields to a same name....and concatenate all your fact tables if possible, so that you can avoid synthetic keys, then build your calendar for MIN & MAX date from your fact data.