Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
william_fu
Contributor 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
MVP
MVP

Re: Join Rows on DateA = DateB

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.

Community Browser