Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
william_fu
Creator II
Creator II

Join Rows on DateA = DateB

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.

1 Reply
petter
Partner - Champion III
Partner - Champion III

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.