Join problem qliksense

[yasminetorkhani]

Hi everyone!

I have a problem with a join in my fact table with the master calendar! I have created a global master calendar with an autonumber key that i would like to have in my fact table.

I have used the key word "resident" in order to get the master calendar key in the fact and join, but the result im getting is a crossjoin with millions of records in my fact table, which is not what i want!

What am i doing wrong ??

Here is my code:

FactEntréeSortie:

        LOAD

              "ID MasterCalendar" Resident MasterCalendar;

        Right Join // keep gives the same result as join

        LOAD   

              AutoNumberHash128(Day("Start Date"), Month("Start Date"),Year("Start Date")) as "ID CalendrierStart",

              AutoNumberHash128(Day("End Date"), Month("End Date"),Year("End Date")) as "ID CalendarEnd",

              Keyas "ID Person";   

// SQL QUERY

    SQL SELECT Key,

              TO_CHAR (A.DATENT, 'DD/MM/YYYY') as "Start Date",

              TO_CHAR (A.DATSOR, 'DD/MM/YYYY') as "End Date"

    FROM Table ;

[wdchristensen]

Might try something like...

FactEntréeSortie:

        LOAD

              "ID MasterCalendar" as "ID Person"

                Resident MasterCalendar;

                // Use the same name and Qlik with perform a full outer join based on the common name autoMagically

        LOAD  

              AutoNumberHash128(Day("Start Date"), Month("Start Date"),Year("Start Date")) as "ID CalendrierStart",

              AutoNumberHash128(Day("End Date"), Month("End Date"),Year("End Date")) as "ID CalendarEnd",

              Keyas "ID Person";  

    SQL SELECT Key,

              TO_CHAR (A.DATENT, 'DD/MM/YYYY') as "Start Date",

              TO_CHAR (A.DATSOR, 'DD/MM/YYYY') as "End Date"

    FROM Table ;

[yasminetorkhani]

I actually would like to have a direct link with the master calenar with that ID, and that won't be the case here..

[wdchristensen]

Can you provide an example of your input and your desired output?

[yasminetorkhani]

Okay, so I want my model to look like this:

The result i'm getting is a cross join where each person id = key is duplicated nrow number of the master calendar, which is not what I want.

Do i need a where clause where it knows the startdate and enddate of the person in order to give it the right master calendar id ??