How to check if a value in Column A match Column B

[ciphergod]

Hi,

Currently facing this problem where i am getting invalid results using IF function in chart(specifically table) to match values from one column to another.

It is difficult for me to provide sample data as the information is quite expansive and restricted, but here's the gist of the situation.

Important columns:

File 1: Name, Appointment

File 2: Appointment, Mandated Course to Attend

File 3: Name, Course Attended, Date Attended

File 1 linked to File 2 via Appointment, File 1 linked to File 3 via Name.

Basically File 1 and 2 will combine to give a list of course mandated for a particular person to attend, while File 3 will show what courses that particular person has attended.

At the moment i am using a master dimension created to find the Status of the person's course.

=if(match([Mandated Course to Attend],[Course Attended]),[Date Attended],'Send for Course')

However i face the issue whereby the created dimension is able to produce the Date Attended for entries that matches, but it also adds in a row which says 'Send for Course'.

I suspect it is due to QS running through the list, finding a match and producing the Date Attended, but produced the other row as it continues to match other entries which does not give a match. Or could be that the data are inappropriately linked

Any idea if my theory is right or wrong? If so/Else, how do i resolve this such that it gives an exclusive output result instead.

TIA

[ciphergod]

any possible solution?

[lorenzoconforti · Accepted solution]

The behaviour you are seeing is normal given the data structure you've implemented

Essentially you have File 1 in the middle with potentially one to many relationship with File 2 and to File 3

So, when you are trying to compare File 2 and File 3 you have a many to many relationship and, when checking for Date Attended, you will bring in all the possible Date combination valid for a specific person, all the courses the person has attended (not just the mandated courses ones)

You need to work on your data structure; join File 1 and File 2, create a new key to the tables where the name of the person is concatenated to the course name

See example attached

[ciphergod]

Hi lorenzo,

Agreed with what you said, but slightly confused how to go about doing it looking at you load editor.

I assume the right way is to use Join instead of Concat. So the only way is to do it through Load Editor?

Correct me if i am wrong, the logic of your solution by using Join is,

Based on a Name and Role, the Join will link the Role from 2 files together. So under each Name and Role, multiple lines of Courses will appear? 

[lorenzoconforti · Accepted solution]

First, we join file 1 and file 2 (the left join) so that we can have in the same table the name of the person and the mandated course

Then in the new table we create a new field (NameCourse) with the name of the person and the course to attend

We create a similar new field in file 3 with the name of the person and the course to attend

Now the two tables have a unique link (name of person and course) which solves the problem you were having originally with the 3 tables (having the extra table in the middle was creating a situation where trying to link elements from the 1st to the 3rd table was returning more results than expected; many to many)

[ciphergod]

Hi Lorenzo, Got my app working. Tried something similar to what you suggested. 

I basically loaded my data using script editor instead of data manager, did a left join on file 1 / 2 to make it a new file. then associated that with my file 3. did a sanity check on the data after doing that and all is correct. 

will accept your answer as solution for the suggestion on left join!