Replicate effects of a v-lookup

Reply
Highlighted
Green Belt

Replicate effects of a v-lookup

I'm trying to have an algorithm that searches one column to see if that value repeats in another column, and if it does return true, else, return false. I can't seem to figure out the case statements that will let me do this, so if list of colors column has red, and list of paints contains red, I want true, if red appears nowher in list of paints return false. Can someone give me a pointer on this?


Accepted Solutions
Orange Belt

Re: Replicate effects of a v-lookup

If you have already joined the datasets together on the fields you are searching, your beastmode can essentially be the "case when" statement from the MySQL example. 

 

CASE WHEN `paint` is null then 'FALSE' else 'TRUE' end

 

This will return "FALSE" if your join didn't find a match and will return "TRUE" if it did.

 

Let me know how this works.

 

_tyson

View solution in original post


All Replies
Orange Belt

Re: Replicate effects of a v-lookup

You could try joining your dataset to a distinct list of the values in the column you are scanning for matches. Something like this in MySQL:

 *Please note that the dataset placeholders in the code below can both be using the same dataset. In other words, you can join a dataset back to itself (or a subset of itself) which sounds like what you are trying to do.

select
a.`color`
,case when b.`paint` is null then 'FALSE' else 'TRUE' end as `your_indicator`
from
[your_table_with_colors] a
left join
(
select
distinct
`paint`
from
[your_table_with_paints] 
) b on a.`color` = b.`paint`

 

_tyson
Green Belt

Re: Replicate effects of a v-lookup

Right, so the data is already joined, I have the distinct list attached to the set I want to look through already, data join isn't the problem, I need to figure out the beast mode syntax for this within the table, which I can't seem to crack. 

Moderator

Re: Replicate effects of a v-lookup

@tyson_c, can you provide more insight for bradsafeguard?

Orange Belt

Re: Replicate effects of a v-lookup

If you have already joined the datasets together on the fields you are searching, your beastmode can essentially be the "case when" statement from the MySQL example. 

 

CASE WHEN `paint` is null then 'FALSE' else 'TRUE' end

 

This will return "FALSE" if your join didn't find a match and will return "TRUE" if it did.

 

Let me know how this works.

 

_tyson

View solution in original post

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!