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

  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 🔵
    Accepted 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.

     

Answers

  • 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..?

  • 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.

  • 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.