Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ;
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 ;
I actually would like to have a direct link with the master calenar with that ID, and that won't be the case here..
Can you provide an example of your input and your desired output?
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 ??