Expression

[shahappsheet]

Hi Guys!

Hope all of you are doing fine.

I have two tables as follows.

Table 1					Table2
KeyColTab1	Col1	Col2	Col3		KeyColTabl2	Col1	Col2	Col4

It can be seen there are two common columns (Col1 & Col2).  I need to create a virtual column in the Table 2. I need to see the value of col3 from table2 when Col1 and Col2 is same between two tables. I used the following expression in the virtual column formula of Table2-

LOOKUP(concatenate(Col1, Col2),"Table1", concatenate(Col1, Col2),"Col3")

But it doesnt work, Please help to shoot the error.

Thanks and Regards.

[TeeSee1]

 I believe the issue is that the third parameter of LOOKUP should be one of the cols of Table 1.

Try using SELECT instead

ANY(
 SELECT(table1[col3],
  AND([col1]=[_thisrow].[col1], [col2]=[_thisrow].[col2])
 )
)

[shahappsheet]

Thanks Tee. Actually, I tried this way also. However, let me check again. In SQL it is very simple, but here I can not do yet. Get back again.

Regards.

[shahappsheet]

Hi Tee! I have tried again following your suggestion. Actually, I tried it before. It should work but it doesn't work. I dont know. Please help me. I need it to work. It is very basic. But I am undone. Very upset.😭

[SkrOYC]

Can you post the result of the Test button from the expression assistant?

[shahappsheet]

Hi @SkrOYC ! Thanks for your response.

Do you mean the data set I am getting?

[SkrOYC]

This

[shahappsheet]

My result is big. I dont know how can i send you.

[shahappsheet]

Please find it.

[Suvrutt_Gurjar]

You had mentioned two columns being compared in the first post. In the expression there seems to be just one column. Could you share details why so?

[shahappsheet]

Thanks Gurjar. Actualy there are two columns but, it was concatenated. I have tried it keeping them separated also. I concatenated as I used LOOKUP. Also applied SELECT but the result is same.

[Suvrutt_Gurjar]

Thank you. You may want to check your concatenation expression and column type in the two tables are identical. The column 1 , Column 2 , Column 3 , Column 4 types are identical.

A quick testing with similar test set up showed that it works correctly as expected with the expressions you have or colleagues have suggested previously.

Table 2 with test results

Edit: Minor edits to the description.

[shahappsheet]

Gurjar, Thanks a lot. I agree, but I dont know it doesnt work as I have already uploaded the test result. In my case the targeted column is boolean. In my case sometimes it shows only the true and the other one is blank. I am so upset.

Anyway, thanks a lot for your time. I will see again.

[Suvrutt_Gurjar]

Got it. Can you please try by wrapping with ANY() such as ANY(SELECT.....  ) around your SELECT() statement?

ANY(SELECT(Assess_Basic[AssessAssignedToStudents], [SemDescCourseCode]= [_THISROW].[SemDescCourseCode]))

Also since you are using Boolean value this means , I believe the table 1 from where you are getting this value should have only one row that has this column value of TRUE or FALSE. or else if SELECT() returns a list of combination of TRUE and FALSE related to many rows from Table 1,  the result will be erroneous.

[shahappsheet]

Thanks @Suvrutt_Gurjar .

Yes, I tried wrapping with ANY also without any luck.

You are correct the value i am looking for from table 1 is Boolean type. I dont know whether in this scenario I need to handle it differently. There is no error but it behave weirdly. 

Thanks a lot.

[Suvrutt_Gurjar]

Thank you. Is your data such that the SELECT() statement likely to return multiple rows?

Could you also share a couple of data examples of the columns [SemDescCourseCode] in both the tables?

[shahappsheet]

It must return one row all the time.

Tried the follows alternatively having the same faulty result-

LOOKUP(
[_THISROW].[SemDescCourseCode],"Assess_Basic", "SemDescCourseCode","AssessAssignedToStudents"
)

-------------------------------------------

ANY(
SELECT(Assess_Basic[AssessAssignedToStudents],
AND([_thisrow].[SemDesc]=[SemDesc], [_thisrow].[CourseCode]=[CourseCode])
)
)

[shahappsheet]

Many instances, I faced reasonless issues with appsheet leaving any error.🙄

[Suvrutt_Gurjar]

Thank you. As requested can you share how data values look for [SemDescCourseCode]? You can of course share only non confidential simulated data. Just wish to know the column data pattern. I am asking because in the image shared by you there are some special characters and commas in some column values? Hence the question.  The highlighted columns below.

[Suvrutt_Gurjar]

Just an update. It works perfectly for me under exact similar testing conditions. It pulled yes and no from other table for matching [Name] and {Email] in that table.

I would request you to take a look at your concatenation, column contents  for special characters etc. The expression works as expected.

[shahappsheet]

I know Mr @Suvrutt_Gurjar . It also works for me in other simple table. But here without any reason, it works weirdly. It only gives one value either TRUE or FALSE. I have called it in the formula of virtual column.  I am tired with AppSheet. 

Anyway, Thanks.

[Joseph_Seddik]

Instead of CONCATENATE why do you not just do a normal SELECT match based on two columns? 

ANY( SELECT(table[column 3], AND(
  [column 1] = [_ThisRow].[column 1],
  [column 2] = [_ThisRow].[column 2]
)))

[shahappsheet]

Thanks @Joseph_Seddik. You are correct. Actually, I wanted to try with both expressions (LOOKUP/SELECT).  Therefore, I concatenate and used in LOOKUP. But in both of the cases result is same. 

[Joseph_Seddik]

@shahappsheet Thank you. Please post a screenshot showing the result of SELECT with AND, as suggested by @TeeSee1, and indicate why you consider it faulty. Thanks!

[Suvrutt_Gurjar]

@Joseph_Seddik  This expression was also suggested by @TeeSee1 in his first post itself

After all this association in the thread, I have a feeling that @here is some possible issue with the data format of those columns involved in the expression. Column 1....Column4

[Joseph_Seddik]

@Suvrutt_Gurjar Thanks my friend, sorry I missed it. 

I've read the post again, more in detail, unless I'm missing something, again :), I noticed he's never posted a result showing the use of two-column select nor indicated the "faulty result". Just a suggestion; I'll let you kindly lead. 

[shahappsheet]

@Suvrutt_Gurjar , I will check carefully with cool head and let you know. Thanks.

[Suvrutt_Gurjar]

Hi @Joseph_Seddik : Never mind. Please do pitch in.  It always helps if a problem is analyzed from different angles.

@shahappsheet  did post a test result with concatenated column. However I requested him for data patterns of the involved columns, that possibly could suggest more.

Hi @shahappsheet : Sure. If you can share data pattern ( of course test data and not real data)  and and types of your columns, the community could give another try. 🙂

[Joseph_Seddik]

Thanks dear @Suvrutt_Gurjar !

Yes, I've seen the result only with concatenated column. In addition to what you've asked, I've asked him to post also the result using SELECT with AND on two columns.