3 Replies Latest reply: May 15, 2014 10:12 AM by Olly Bond RSS

    Using the same lookup multiple times in a model

    MonUserCJ _

      Hey all,

       

      I have a data source that has these shorthand descriptor codes. There are 3 fields for these codes. I have a mapping from the codes to the descriptions. I want to use it for all 3 fields (they all use the same code mapping). It doesn't seem like I'm able to add multiple mappings of source columns to lookup columns.

       

      Is there a way to do this? I can create three separate lookups with 3 copies of the mapping file, but I thought there might be a better way. I'd appreciate any advice that anyone can give.

       

      Thanks.

        • Using the same lookup multiple times in a model
          Olly Bond

          Hello CJ

           

          Is your source a report or a table? If it's a report, can you post a sample?

           

          I've got colleague who does something similar in one of his projects - one model looks up a source once using one key, and again using a second - but he's not had to use multiple copies.

           

          Best wishes,

           

          Olly

            • Using the same lookup multiple times in a model
              MonUserCJ _

              The source of the mapping is a report. It's confidential, so I'd rather not post it here, but it's basically like:

               

              Code                         Description

              ABC                          Green

              DIJ                          Yellow

              SOA                          Blue

               

              /CODE

               

              And then 3 columns I have in the table generated from the main report use the codes.

               

               

              Column 1           Column 2             Column 3

              ABC                   SOA                     DIJ

              SOA                   DIJ                     ABC

               

              /CODE

               

              There are more code than that (not all codes have to be used on a row). But basically it just seems like there isn't a way to use one lookup to populate multiple columns that use the mapping--you have to create a lookup for each one.  That's what I did to get around it in the meantime. Any idea on how to do it in a more elegant fashion?

                • Using the same lookup multiple times in a model
                  Olly Bond

                  Hello CJ

                   

                  If you're trying to make a lookup from sample 2 to sample 1 in your post, then yes, you'd need three lookups, although you shouldn't need three copies of sample 1 to make these work.

                   

                  There is an easier way, possibly, if sample 2 is a report. You can always export a table to a report as fixed width text if you need to convert it. Then you can use the multi column region to convert sample 2 to:

                   

                  Column; Row; Value

                  1; 1; ABC

                  1; 2; SOA

                  2; 1; SOA

                  2; 2; DIJ

                  3; 1; DIJ

                  3; 2; ABC[/CODE]

                   

                  From here, it should be easy to make a lookup to Sample 1 to bring in the colors as a single lookup.

                   

                  HTH

                   

                  Olly