Beast Mode vlookup

d_ventures
d_ventures Member
in Archive

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!

 

LIST1LIST2MATCH?
36281573615no
47236147626no
47381236281yes
95881573461no
5736184771no

Comments

  • cmarkum
    cmarkum Contributor

    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
    d_ventures Member

    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
    kshah008 Contributor

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

  • jehat
    jehat Domo Employee

    @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
    rado98 Contributor

    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
    miken12r Domo Employee

    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! 

Categories