How do I lookup data from another data source? e.g. Validate US State abbreviation

user03839
user03839 Member
in Connectors

  New to Domo, but masterful with Excel/VBscript/Formulas - scenario, I have uploaded a spreadsheet with rows of data that have a 2 character STATE code column. 

1. Source "A" -- The States can consist of US States or Canadian regions. 

2. Source "B" -- I have added a separate data source spreadsheet to list/maintain US State and Canadian region codes, for use with "any" additional datasource

3.  How can data source "A - any" have a default filter applied to show only US data based on matching the State Code against datasource "B-StateCode"?

4.  I think of this almost like a SQL statement, SELECT * from SourceAworkbookexcel where State IN SourceBworkbookexcel..??  But not sure of the format/application in DOMO, treating the workbooks as tables?

Best Answer

  • AS
    AS Coach
    Answer ✓

    The only way in Domo to have datasets affect each other is to join them using a dataflow.

    You can set up a dataflow with a join a lot like that, and create a third output dataset, C, with which you'll build your cards.  That way whatever states are in source B will affect how A and B produce output C.

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    The only way in Domo to have datasets affect each other is to join them using a dataflow.

    You can set up a dataflow with a join a lot like that, and create a third output dataset, C, with which you'll build your cards.  That way whatever states are in source B will affect how A and B produce output C.

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user03839
    user03839 Member

    For those interested, I used Magic ETL, joined the two sources on the STATE column, then filtered out duplicate rows....I believe there is a more efficient way as this may temporarily greatly inflate the dataset with not explicitely stating the type of join..?

  • AS
    AS Coach

    Duplication might be reduced naturally, depending on the direction and cardinality of the join in your ETL join transform.  If you're willing to share a snippet of sample data we could probably look at that more closely.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jhl
    jhl Member

    If I am understanding this correctly, you are not sure about the type of join you are using?

    Just FYI, you can specify the type of join to use in Magic ETL by clicking on the symbol between the two datasets you will see when editing the "Join Data" step.

Categories