Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yasminetorkhani
New Contributor

Join problem qliksense

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 ;

           

4 Replies
wdchristensen
Contributor III

Re: Join problem qliksense

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
New Contributor

Re: Join problem qliksense

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
Contributor III

Re: Join problem qliksense

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

yasminetorkhani
New Contributor

Re: Join problem qliksense

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

modele.PNG

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.

tab.PNG

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 ??

Community Browser