Forum Discussion

MrMarshall's avatar
MrMarshall
Helper II
8 years ago
Solved

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 2...
Need Help
  • Thejeswar's avatar
    Thejeswar
    8 years ago

    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!!!

     

     

Thejeswar's avatar
Thejeswar
Super User
8 years ago

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!!!

 

 

MrMarshall's avatar
MrMarshall
Helper II
8 years ago

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?

  • MrMarshall's avatar
    MrMarshall
    Helper II
    8 years ago

    Nvm, I figured it out! Created a list.
    List.Dates([StartDate], 100, #duration(1,0,0,0))