Forum Discussion
New Column Lookupvalue with a filter on another Tab
Hi together,
I have to check if in the table "data_Current" the email is matching with an email in the table "users" and it should only match when the User in the table "users" does have an model "IOS" or "Android" in table "devices".
Table Data_Current
Imei | Username | |
1234 | test.1 | |
12345 | test2 | |
123456 | test2 | |
null | test3 | |
1212 | test100 |
Table Devices
User-ID | IMEI | SERIAL Number | Model |
1 | 12345 | abcde | Andorid |
2 | 22222 | abce | Android |
3 | 34333 | aaaa | IOS |
3 | 33456 | aaaae | Dell |
4 | 12345 | ertssd | Dell |
5 | 111111 | asdas | IOS |
Table Users:
USER-ID | Name | |
1 | Meyer | |
2 | Lampe | |
3 | Anton | |
4 | Magda | |
5 | Meyer |
My suggestion would be to create a new column in the table current_data if the email[current_data] is in the table [users] and the user in table [users] has a model ("Android" or "IOS") in Table [devices].
The solution should be:
Imei | Username | NEW COLUMN | |
1234 | test.1 | Yes | |
12345 | test2 | No | |
123456 | test2 | No | |
null | test3 | Yes | |
1212 | test100 | Yes |
9 Replies
- amitchandak
Super User
Anonymous ,
new column= var _1 = count(User, current_date[email] = user[email]),user[email])
return
if(isblank(_1) , "No", "Yes")
- vojtechsima
Hi, Anonymous ,
I tested your sample data and you got an error in the Devices table as IMEI should be unique, however, with modifying one row, here is my working solution:Check = var Model_Check = LOOKUPVALUE(Devices[Model],Devices[IMEI],Current_Data[IMEI]) var MAIL_Check = LOOKUPVALUE(Users[EMAIL],Users[EMAIL],Current_Data[Email]) RETURN IF(Model_Check="Android" || Model_Check="IOS" && NOT(ISBLANK(MAIL_Check)), "Yes", "No")Please note, you only need the "Check" column, I added the two more just for you to clarify what'S going on.
- vojtechsima
Hi,
Anonymous
I found a few inconveniences in your sample data:
If you wanna relate the tables using "user ID", [email protected] cannot Return True, also you have duplicity in Devices, meaning the Lookup can't tell which row should be assigned to. Also, you have a typo in [email protected], his device is "Andorid", not "Android", thus it can't return TRUE.
If I delete duplicity and ignore Test10, here is my solution:Check = var Model_Check = LOOKUPVALUE(Devices[Model],Devices[User-ID],Current_Data[UserID]) var UserID = RELATED(Users[USER-ID]) RETURN IF(Model_Check="Android" || Model_Check="IOS" && NOT(ISBLANK(UserID)), "Yes", "No")
