How to set up lookups in Imply Cloud

Follow

Comments

1 comment

  • Dhaval Patel

    we have started using lookup for multi-columns lookup file, you guys may already know this but just want to share our approach if it is helpful or you guys want to flag it for the specific reasons.

    For example: 

    hdfs_msdrg_lookup.csv

    Lookup is pipe delimited and all values after Pipe are comma separated. We can extract each value after pipe as an individual column. This will help us use one lookup table per dim table containing many attributes instead of creating one lookup file per attribute.

     

    558|attribute1, attribute2, atrribute3

    Formula to extract 1st attribute=$msdrgcode.lookup(hdfs_msdrg_lookup).extract('(.*)_.*_.*')

    Formula to extract 2nd attribute=$msdrgcode.lookup(hdfs_msdrg_lookup).extract('.*_(.*)_.*')

    Formula to extract 3rd attribute=$msdrgcode.lookup(hdfs_msdrg_lookup).extract('.*_.*_(.*)')

Please sign in to leave a comment.