Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Considering the two fact tables below, how can I link them where the dates match? (ReferenceDate.YearMonth = PaymentDate.YearMonth)
ReferenceDate.YearMonth | ID | Group |
---|---|---|
2017-Nov | 471394 | DEF |
2017-Dec | 471394 | XYZ |
2018-Jan | 471394 | DEF |
PaymentDate.YearMonth | ID | Amount |
---|---|---|
2017-Dec | 471394 | 211.075 |
2018-Jan | 471394 | 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.YearMonth | ID | Group | Amount |
---|---|---|---|
2017-Dec | 471394 | XYZ | 211.075 |
2017-Dec | 471394 | DEF | 211.075 |
2018-Jan | 471394 | XYZ | 258.545 |
2018-Jan | 471394 | DEF | 258.545 |
This is the desired output:
PaymentDate.YearMonth | ID | Group | Amount |
---|---|---|---|
2017-Dec | 471394 | XYZ | 211.075 |
2018-Jan | 471394 | DEF | 258.545 |
A common date dimension seems to be the way to go, but couldn't get it to work.
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.