1 Reply Latest reply: Apr 3, 2018 1:58 AM by Petter Skjolden RSS

    Join Rows on DateA = DateB

    William Fu

      Considering the two fact tables below, how can I link them where the dates match? (ReferenceDate.YearMonth = PaymentDate.YearMonth)

       

       

      ReferenceDate.YearMonthIDGroup
      2017-Nov471394DEF
      2017-Dec471394XYZ
      2018-Jan471394DEF

       

       

      PaymentDate.YearMonthIDAmount
      2017-Dec471394211.075
      2018-Jan471394

      258.545

       

      In the current scenario, adding the Group as a column returns all possible outcomes for each row in the payment table, giving me duplicates:

          

      PaymentDate.YearMonthIDGroupAmount
      2017-Dec471394XYZ211.075
      2017-Dec471394DEF211.075
      2018-Jan471394XYZ258.545
      2018-Jan471394DEF258.545



      This is the desired output:

       

      PaymentDate.YearMonthIDGroupAmount
      2017-Dec471394XYZ211.075
      2018-Jan471394DEF258.545


      A common date dimension seems to be the way to go, but couldn't get it to work.

        • Re: Join Rows on DateA = DateB
          Petter Skjolden

          Qlik will do it correctly if you make sure that both the date and the ID are used to join or associate (link)  the two tables. To be able to do that both the fields must be named the same in the two tables. A join in the load script where you rename for instance ReferenceDate.YearMonth to just PaymentDate.YearMonth when you bring in the tables and before the join will do the trick.

           

          In a load script it would look like:

           

          LOAD

          ID,

          ReferenceDate.YearMonth AS PaymentDate.YearMonth,

          Group

          .....

          ;

           

          INNER JOIN

          LOAD

            ID,

            PaymentDate.YearMonth,

            Amount

          .....

          ;

           

          Qlik has to rely on a natural join where all the fields that should match have to have corresponding names in the two tables.