Beast Mode vlookup

Hi!

I am trying to figure out if I can do a vlookup in Beast Mode to look up two of the same numbers (LIST1, LIST2) in two different columns and then outout in the third (MATCH?)? The below also explains what I am trying to do. As of right now I cannot figure it out. Any help is much appreciated!

LIST1	LIST2	MATCH?
36281	573615	no
472361	47626	no
473812	36281	yes
95881	573461	no
57361	84771	no

Find more posts tagged with

Comments

cmarkum

There's not really a VLOOKUP function in DOMO. MySQL has a EXISTS function (e.g. SELECT COLUMN1 FROM T1 WHERE EXISTS (SELECT * FROM T2) but I don't believe that is available in BeastMode. The obove compares the value from two tables. The functionality will still exists by using a subquery. If you have MySQL available in Dataflows you can use the EXISTS function to compare the two. If not, you can use the MagicETL process and have the list setup as two differnet sources and combine them using the MagicETL features.

d_ventures

Thanks for the insight! The only option you mentioned would be the last one using MagicETL but the issue with that is we don't want to combine the data. We just want to only show or display rows that have a match in 'LIST2'. Do you know if this is possible?

kshah008

@cmarkum, can you help @d_ventures with their follow-up question?

jehat

@d_ventures Will you send an email to joseph.ehat@domo.com and tell me which instance you are in and which two datasets you want to do this for? Please reference case #02035438.

Joe Ehat

Domo Support

rado98

How about something like this in MagicETL

Select Columns from the DataInput LIST1 only

Select Columns from the DataInput LIST2 only, as a separate stream

InnerJoin SelectColumns1 and SelectColums2.

This should output a list of only the repeated values.

Add the constant "yes" to Join1

Now join again the DataInput with Join1 and you should have a column with "yes" on each repeated value.

There is probably a way to add the "no" on that ETL but I cant think of it at the moment but you can easily do that in Beastmode.

miken12r

I've requested the "vlookup" feature, as well -- and for exactly the same scenario. Had to use dataflows in the meantime, which works great but I'd still prefer to do this in beastmode when available. will definitely push the product team on this. thx for the feedback!