Forum Discussion

qwertzuiop's avatar
qwertzuiop
Advocate III
5 years ago
Solved

Add Attributs form another table

Hi dear community   Following problem to solve: I habe two tables (only an overview of data):   Table 1 contains information for which period an employee is assigned to which region / location /...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    qwertzuiop , New Columns like this in table 2

     

    Area = maxx(filter(Table1, Table1[ID] =Table2[ID] && Table2[Date] >= Table1[Date_from] && Table2[Date] < = Table1[Date_to]),Table1[Area])

     

    Same way repeat other two

     

    Location = maxx(filter(Table1, Table1[ID] =Table2[ID] && Table2[Date] >= Table1[Date_from] && Table2[Date] < = Table1[Date_to]),Table1[Location])
    Group = maxx(filter(Table1, Table1[ID] =Table2[ID] && Table2[Date] >= Table1[Date_from] && Table2[Date] < = Table1[Date_to]),Table1[Group])

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi qwertzuiop 

You can choose one of  2 solution.

1) Create a relationship with Table1(ID)-->Table2(ID) and create columns with Related() function . For example: create column in Table2 as Area=Related(Table1[Area]) and other columns

2)Use LOOKUPVALUE() Function without relations. for example: Create Column in Table2 as Area=LOOKUPVALUE(Table1[Area],Table1[ID],Table2[ID]) 

  • qwertzuiop's avatar
    qwertzuiop
    Advocate III
    5 years ago

    Thank you very much for your contribution, but I think it's not thas quick solved.

     

    Why? Because the ID can/will occur several times in both tables, since the employee in table 1 may change the area every month and in table 2 he/she carry out a new shift every day.

     

    So I have to check, in which time period (from-to) the date of the shift fits and matches the id.

    Then it should be possible for me to tell you later, which employee performs a shift in which region based on the date.

     

    Got it?

    Thaaanks a lot.

     

    Cheers

    qwertzuiop

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      qwertzuiop , New Columns like this in table 2

       

      Area = maxx(filter(Table1, Table1[ID] =Table2[ID] && Table2[Date] >= Table1[Date_from] && Table2[Date] < = Table1[Date_to]),Table1[Area])

       

      Same way repeat other two

       

      Location = maxx(filter(Table1, Table1[ID] =Table2[ID] && Table2[Date] >= Table1[Date_from] && Table2[Date] < = Table1[Date_to]),Table1[Location])
      Group = maxx(filter(Table1, Table1[ID] =Table2[ID] && Table2[Date] >= Table1[Date_from] && Table2[Date] < = Table1[Date_to]),Table1[Group])

      • qwertzuiop's avatar
        qwertzuiop
        Advocate III
        5 years ago

        Hi amitchandak 

         

        Sorry for my absence.

        Thank you very much for your support.
        The solution you gave me here works.

         

         

        Have a nice day - you saved mine 😉