4 Replies Latest reply: Mar 29, 2018 12:27 PM by Yasmine Torkhani RSS

    Join problem qliksense

    Yasmine Torkhani

      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 ;

                 

        • Re: Join problem qliksense
          William Christensen

          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 ;