Forum Discussion

pavia's avatar
pavia
New Contributor II
2 years ago
Solved

Create Expression Library from Excel data

I have a couple situations where there are lists of values that will need to be referenced and those lists will change in different runs of the pipelines. 

Since they will change, updating mappers or conditionals doesn't seem ideal, so am thinking that creating and using expression libraries would work well but I'm struggling to convert the data into one and use it.

For example, I have the following Excel table that is being read in and parsed 

I want to be able to look up the CovCode in a different pipeline and return the Plan or Tier for the associated code.

3 Replies

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    2 years ago
    Copy Link

    pavia 

    You can put that excel file in some file location(where will be periodically refreshed) and read that file from there, so you always will have the latest changes. 

    About the lookup, you can do that by using the Join Snap and perform a join on CovCode field.

  • pavia's avatar
    pavia
    New Contributor II
    2 years ago
    Copy Link

    I'm not clear how a Join would work here for a lookup. Along with other transformation taking place, I want to look up the values and pass them into different fields based on finding the matched code so I am envisioning it happening in a mapper snap.

    So my original file could have Name and the CovCode, but the result file might have Name, some other hardcoded fields, Plan, and Tier.

  • pavia's avatar
    pavia
    New Contributor II
    2 years ago
    Copy Link

    I was able to get the result I was looking for by doing a Group by N with a group size of 0 and then a JSON formatter with Format Each Document and Pretty-Print checked. Then used a File Writer to create the expression library with the covcode data. Then I created another expression library with an expression to do a lookup from the original data fields to the covcode list that I could reference easily in a mapper.  There were multiple places in my raw data that contained these covcodes, so this approach seems to reduce some additional snaps for me.