Forum Discussion
Relation problem through lookuptable
I have two tables, and a relation between them through a lookup table.
Related with ID & UserID as:
Sales:
| UserId | Profit | Date |
| 1 | 2000 | 2018-02-01 |
| 1 | 2000 | 2018-02-01 |
| 1 | 200 | 2018-02-02 |
Users:
| Id | Firstname |
| 1 | Andrew |
Other
| UserId | OtherValue | Date |
| 1 | 5 | 2018-02-01 |
| 1 | 6 | 2018-02-02 |
| 1 | 4 | 2018-02-02 |
No relation errors. I am testing this with a simple matrix, as you can see, the OtherValue column is wrong, it should be as stated with red.
What am I doing wrong?
Hi MrMarshall,
This is happening because you tables are joined only using UserID when the data is present for multiple days.
To Solve this join your tables using a concatenated column which is a concatenation of UserId and Date
Screenshots for the same are given below
concatenated column
Users TablesNew relationshipFinal Output
Hope this helps!!!
3 Replies
Hi MrMarshall,
This is happening because you tables are joined only using UserID when the data is present for multiple days.
To Solve this join your tables using a concatenated column which is a concatenation of UserId and Date
Screenshots for the same are given below
concatenated column
Users TablesNew relationshipFinal Output
Hope this helps!!!
Hi!
I understand now. That was really helpful, Thx!
Although, it requires that I would create a Date value for each unique user.
I cannot seem to get it to work in an automated way, say if I have 100 users.
Any ideas?Nvm, I figured it out! Created a list.
List.Dates([StartDate], 100, #duration(1,0,0,0))
